CODEWITHSUNDEEP
vbaexcelexcel-formula
Boosted_d16
Boosted_d16

Reputation: 14062

Run-time error 1004 Application-defined or Object-defined error when setting Formula property

This works fine:

Sub Button1_Click()

    Dim strFormulas(1 To 3) As Variant

    With ThisWorkbook.Sheets("TRANSFORM")
        strFormulas(1) = "=SUM(A2:B2)"
        strFormulas(2) = "=PRODUCT(A2:B2)"
        strFormulas(3) = "=A2/B2"

        .Range("A4:G4").Formula = strFormulas
        .Range("A4:G20").FillDown
    End With

End Sub

But this doesn't work - everything is the same besides strFormulas(1): 

Sub Button1_Click()

    Dim strFormulas(1 To 3) As Variant

    With ThisWorkbook.Sheets("TRANSFORM")
        strFormulas(1) = "=IF(NOT(IMPORT!A4=''),VLOOKUP(IMPORT!A4,'VL1'!$Q$4:$R$26,2,FALSE),'-')"
        strFormulas(2) = "=PRODUCT(A2:B2)"
        strFormulas(3) = "=A2/B2"

        .Range("A4:G4").Formula = strFormulas
        .Range("A4:G20").FillDown
    End With

End Sub

I get this error:

Run-time error 1004 Application-defined or Object-defined error

I think it may be because I'm trying to pull values from another worksheet in strFormulas(1).

I did my research and couldn't find a solution. Can anyone see what the problem is?

Upvotes: 1

Views: 123

Answers (1)

Ralph
Ralph

Reputation: 9434

You got apostrophes ' in the strFormulas(1) where there should be quotation marks ". So, it should be more something like 

strFormulas(1) = "=IF(NOT(IMPORT!A4=""""),VLOOKUP(IMPORT!A4,""VL1""!$Q$4:$R$26,2,FALSE),""-"")"

Just replace all ' within the formula with two quotation marks "".

Upvotes: 2

Related Questions