CODEWITHSUNDEEP
vbaexcelexcel-formularuntime
Reginald
Reginald

Reputation: 13

Excel VBA 1004 Error formulas

I've been working in VBA for about a month now, and I feel like I have a decent command of the language. Unfortunately, that's why this error is so frustrating.

I'm trying to assign a formula to a cell, and I'm not using UK-style separators (so commas, instead of semicolons). I'm still getting the runtime error.

The syntax of the actual formula is fine, as I've tried using it in Excel, and it does what I need it to, so I have to assume the error is in my VBA syntax, below:

    With DecileChooser
    Range("A1").CurrentRegion.PasteSpecial

    Range("I1").Value = CurrentPD
    Range("I2").Value = CompanyDomain


    DecileChooser.Range("J2").Formula = "=IF($I$1 > D2, G2, "")"

    Range("J2:J11").FillDown

    Range("K1").FormulaLocal = "=max(J1:J11)"

    Range("K1").Value = DecileValue
End With

This isn't the entire subroutine, just the operative part.

Upvotes: 1

Views: 183

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Double up on the double quotes:

DecileChooser.Range("J2").Formula = "=IF($I$1 > D2, G2, """")"

or

dq = Chr(34)
DecileChooser.Range("J2").Formula = "=IF($I$1 > D2, G2," & dq & dq & ")"

Upvotes: 0

Tienna
Tienna

Reputation: 11

The issue here is that the code is reading the double quotation within your formula as the end of the formula, ie what it reads is:

DecileChooser.Range("J2").Formula = "=IF($I$1 > D2, G2, "

but then has a syntax issue with the remaining ")"

You have to use double quotation marks to indicate that you are not ending the string.

With DecileChooser
Range("A1").CurrentRegion.PasteSpecial

Range("I1").Value = CurrentPD
Range("I2").Value = CompanyDomain

DecileChooser.Range("J2").Formula = "=IF($I$1 > D2, G2, """")"

Range("J2:J11").FillDown

Range("K1").FormulaLocal = "=max(J1:J11)"

Range("K1").Value = DecileValue
End With

Upvotes: 1

Related Questions