Excel vba error name in formula

0
votes

I'm trying to pass one formula from vba excel to a cell i have this code

   atmFecha = "=IF(L" & tmLastRow & "=0," & Chr(34) & " " & Chr(34) & ",'ws2'!$E$3)"    
    .Cells(tmLastRow, "H").Value = atmFecha

this instruction insert in the cell the correct formula but display #NAME? I have to press "F2" and then "Enter" to Excel recognize the formula and display the correct value.

I use the instructions formula and formulaR1C1 but the result is the same.

what can i do to recognize automaticly the correct value of the formula ?

2
vbaexcel
.Cells(tmLastRow, "H").Formula= atmFecha – Siddharth Rout
I am assuming that atmFecha = "=IF(L" & tmLastRow & "=0," & Chr(34) & " " & Chr(34) & ",'ws2'!$E$3)" – Siddharth Rout
I tried but not works continue displaying #NAME? in the cell, I edit my post to include this function and formulaR1C1. But thx for your comment. – miguelSnam
do you have a sheet called ws2 or is that a worksheet object in your code – Siddharth Rout
Also can you paste the actual formula that you get when you press F2 and Enter – Siddharth Rout

2 Answers

1
votes

After your code, add this line:

Application.Calculate

It sounds like you are set to Manual Calculation, so that line will refresh everything. If you're setting a number of formulas, wait until they're all inserted, then execute that line.

0
votes

I don't know how it fix but now is working, I was testing with the second file I made this is the code" 

Sub btnCalculate()
    Dim atmFecha As String
    Dim tmLastRow As Integer

    With Worksheets("ws1")
        tmLastRow = 1
        atmFecha = "=IF(C1=0," & Chr(34) & " " & Chr(34) & ",'ws2'!$A$1)"
       .Cells(tmLastRow, "A").Value = atmFecha
    End With
End Sub