Excel macro apply a formula but don't evaluate it

Question

I want to use my macro to apply a simple formula to every row of my sheet at column R, evaluating the corresponding cell in column C The formula is simply:

=YEAR(C2)

when C2 will be updated into C3, C4... in the correct row. The line in the macro is

sca.Range("R2:R" & fA2 + fA1 + fA0).Formula = formx

and the formula is written correctly in my sheet sca from my string variable formx

The macro write the formula, but the calculation is not performed. An #NAME error message comes out. Nor hitting ENTER neither using one of other methods suggested here works. But if I wrote the same formula by hand in the near column (or over the auto-written formula), it works: the year is correctly displayed. Even =YEAR(Sheet1!C2) does not work when applied by the macro.

EDIT: the full code as sktneer requested.

Sub myMacro()

Dim sh0 As Worksheet, sh1, sh2 As Worksheet
Dim sca As Worksheet
Dim fA0 As Long, fA1 As Long, fA2 As Long
Dim rng0, rng1, rng2 As Range
Dim Annom0 As String
Dim Annom1 As String
Dim Annom2 As String
Dim formx As String
Dim annomax() As Variant
Dim annomin() As Variant
Dim StartDate As Integer, EndDate As Integer

'extract correct names of sheets from principal sheet
Annom0 = Cells(13, 6).Value
Annom1 = Cells(13, 7).Value
Annom2 = Cells(13, 8).Value

'extract correct number of rows from secondary sheets
fA0 = Sheets(Annom0).Cells(Rows.Count, 1).End(xlUp).Row
Cells(14, 6).Value = fA0
fA1 = Sheets(Annom1).Cells(Rows.Count, 1).End(xlUp).Row
Cells(14, 7).Value = fA1
fA2 = Sheets(Annom2).Cells(Rows.Count, 1).End(xlUp).Row
Cells(14, 8).Value = fA2

Set sh0 = Sheets(Annom0)
Set sh1 = Sheets(Annom1)
Set sh2 = Sheets(Annom2)
Set sca = Sheets("GLOBAL")

'calculate the minimum and the maximum dates
annomax = sh0.Range(sh0.Cells(2, 3), sh0.Cells(fA0, 3)).Value2
annomin = sh2.Range(sh2.Cells(2, 3), sh2.Cells(fA2, 3)).Value2
Cells(15, 7).Value = CDate(Application.Max(annomax))
Cells(16, 7).Value = CDate(Application.Min(annomin))

sca.Cells.Clear
formx = "=YEAR(GLOBAL!C2)"

'create the new sheet copying all the 3 secondary sheets one bunch of rows below the other 
Set rng2 = sh2.Range("A2:A" & fA2)
rng2.EntireRow.Copy sca.Cells(2, 1).End(xlUp)(2)
Set rng1 = sh1.Range("A2:A" & fA1)
rng1.EntireRow.Copy sca.Cells(fA2 + 1, 1).End(xlUp)(2)
Set rng0 = sh0.Range("A2:A" & fA1)
rng0.EntireRow.Copy sca.Cells(fA2 + fA1 + 1, 1).End(xlUp)(2)
'then reorder it
sca.Columns("A:O").Sort key1:=sca.Range("C:C"), order1:=xlAscending, Header:=xlYes
sca.Range("R2:R" & fA2 + fA1 + fA0).Formula = formx
'sca.EnableCalculation = True 'none of these method was useful
'Application.CalculateFull
'Calculate

End Sub

I would look carefully at the contents of formx just before you assign it to .Formula. Could there be a CR or LF there? Or some other special character? How about a bunch of spaces and some other text? — Rich Holton, Apr 22 '17 at 17:07
Use Debug.Print formx or MsgBox formx before applying the formula and check if the string is equivalent to a genuine excel formula. Otherwise #NAME error may occur if you misspell a formula. — Subodh Tiwari sktneer, Apr 22 '17 at 17:08
@RichHolton there is no mispelling. The formula is correct and correctly assigned in the cell. I discovered also that if I select it with the mouse drag and THEN hit Enter, the formula is applied and the year appears in the cell. — marcoresk, Apr 22 '17 at 17:57
@sktneer As written in the question and in the comment above, the formula is correct. Copied-and-pasted by hand in the cell, then works. But there are more than 4000 rows AND my boss wants a macro assigned to a magic button: how to make the formula applied by the macro? — marcoresk, Apr 22 '17 at 18:00
@sktneer Posted. But I think the strange thing is not there. The formula is applied correctly in the right cell. The same formula selected in the bar then hit enter works with no changes. I don't know why does not work after being written by the macro — marcoresk, Apr 22 '17 at 18:12
Very odd...grasping at straws here. Do you have a VBA function with the name Year? Or a Name named Year? Have you tried saving the workbook, exiting Excel, then restarting and reloading? — Rich Holton, Apr 22 '17 at 18:35
Have you tried `Application.Calculation = xlCalculationAutomatic` at the end? — J. Garth, Apr 22 '17 at 18:50
Is that code exactly what you are using? I.e. you haven't translated your code from the Italian `formx = "=ANNO(GLOBAL!C2)"` to the English `formx = "=YEAR(GLOBAL!C2)"` so that we can understand it? (This reminds me of [this question](http://stackoverflow.com/q/43374910/6535336).) — YowE3K, Apr 22 '17 at 19:45
@YowE3K you are right. I translated from Italian and using `formx = "=YEAR(GLOBAL!C2)"` make the trick! (Feel free to post your comment as an answer and I will accept it) — marcoresk, Apr 22 '17 at 20:33
Posted an answer, but then changed my mind. We should mark this question as a duplicate of the other question. (Marking as "duplicate" is not a bad thing - it just directs traffic to the other question - it doesn't mean this question is "bad".) — YowE3K, Apr 22 '17 at 20:43
You should make sure to qualify the ranges in your macro with the sheet you want the info from. I.e. `fA0 = Sheets(Annom0).Cells(Rows.Count, 1).End(xlUp).Row` should likely be `fA0 = Sheets(Annom0).Cells(Sheets(Annom0).Rows.Count, 1).End(xlUp).Row`, etc. That may help, generally anyways, to make positive the `Rows.Count` and other info. is getting pulled from the right place. — BruceWayne, Apr 22 '17 at 21:33

score 0 · Answer 1 · answered Apr 22 '17 at 18:14

0

The cells may be formatted as text. Try changing the format of the cells to General:

With sca.Range("R2:R" & fA2 + fA1 + fA0)
    .Cells.NumberFormat = "General"
    .Formula = formx
End With

answered Apr 22 '17 at 18:14

J. Garth

783
6
10

Thank you for the try, but it wasn't effective. I still obtain the same behaviour (formula written in the cell, #NAME error, if I select the text of the formula than hit ENTER or select another cell, the formula starts to work. – marcoresk Apr 22 '17 at 18:18

Excel macro apply a formula but don't evaluate it

1 Answers1