CODEWITHSUNDEEP
vbaexcel
Daniel Viaño
Daniel Viaño

Reputation: 564

Apply formula in VBA?

I am trying to apply a formula that writes YES or NO in a cell checking if another cell starts with Q) text.

Writing it manually in Excel is working.

=IF(LEFT(A2;2)="Q)";"YES";"NO")

But when I try to do it automatically inside a macro

Range("R2").Formula = "=IF(LEFT(A2;2)=""Q)"";""YES"";""NO"")"

Run-time error '1004': Application-defined or object-defined error

Also, I will want to do it for the whole column, something like this

Range("R2:R" & lastRow).Formula = "=IF(LEFT(A2;2)=""Q)"";""YES"";""NO"")"

But for that I have to solve the first problem before.

What am I doing wrong?

Upvotes: 1

Views: 9721

Answers (5)

Florent B.
Florent B.

Reputation: 42518

The Range.Formula is expecting a EN-US syntax.

Use Range.FormulaLocal to insert a formula based on your regional settings:

Range("R2").FormulaLocal = "=IF(LEFT(A2;2)=""Q)"";""YES"";""NO"")"

Upvotes: 1

Vityata
Vityata

Reputation: 43575

In general, try the following:

  • Make a workable formula in Excel

  • Then select the cell with the workable formula

  • Run the code

  • In the immediate window something useful should be printed.

The code:

Public Sub PrintMeUsefulFormula()
    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula
End Sub

Source: Error with IF/OR in VBA

Upvotes: 1

Tom
Tom

Reputation: 31

You could always try the record macro option: 1. Start recording 2. Paste the formula into a cell 3. Stop recording 4. Look at the recorded macro and how it formats the formula

Upvotes: 1

SJR
SJR

Reputation: 23081

VBA uses the comma separator, irrespective of your local settings

Range("R2:R" & lastRow).Formula = "=IF(LEFT(A2,2)=""Q)"",""YES"",""NO"")"

Upvotes: 2

blackspacer
blackspacer

Reputation: 353

Instead of double double-quotes. try skipping the double quotes with .

For example: instead of ""Q"" try \"Q\"

Upvotes: 0

Related Questions