VBA True False Condition

Question

Can someone help me why im getting error, if i do like this in VBA

Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6;"False";"True")"

Already try like this and still giving error

Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6,"False","True")"

Answer 1

The issue in your VBA code is related to how Excel expects formulas to be formatted, particularly with regard to the use of quotation marks for text strings and the decimal/thousand separator. Here are a few points to consider:

1. Quotation Marks for Strings: Excel formulas that contain text strings should use double quotation marks around the text. In VBA, you need to use double quotation marks twice to escape them properly.

2. Comma vs. Semicolon: Depending on your Excel regional settings, the list separator may be a comma or a semicolon. In many regions, the list separator is a comma, not a semicolon.

3. Formula Localization: If your Excel settings use a comma as a decimal separator, you might need to use a semicolon to separate the arguments of functions. Otherwise, a comma is used.

Here is how you can correct your code:

For English Locale (Comma as List Separator)

Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6, ""False"", ""True"")"

For European Locale (Semicolon as List Separator)

Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6; ""False""; ""True"")"

The key here is using "" (double quotation marks twice) to properly escape the quotation marks in the string.

Example Code

Sub ApplyFormula()
    ' For English Locale
    Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6, ""False"", ""True"")"

    ' For European Locale
    ' Worksheets("ME2N").Range("AB6:AB31000").Formula = "=IF(AA6<T6; ""False""; ""True"")"
End Sub

By using the correct format, you should avoid syntax errors and successfully apply the formula across the specified range.