CODEWITHSUNDEEP
excelvbaexcel-formula
KoderM16
KoderM16

Reputation: 157

Using VBA to insert formula - IF statement with blanks

I am using VBA to enter a formula into a range of cells. The formula is:

If VAT = "YES" Then
Range("G24:G47").Formula = "=IF(F24="","",G24*0.14)"
ElseIf VAT = "NO" Then
Range("G24:G47").Formula = "=IF(F24="","",G24*0)"
Else
'Do Nothing
End If

Basically it checks the variable VAT to see whether it is YES or NO and then inserts the corresponding formula. The issue is that the formulas for both options output as follows (I am using VAT = YES as the example): =IF(F24=",",G24*0.14). The problem is that one " disappears from the logic step and the if true portion. Any help on this would be really appreciated.

Upvotes: 0

Views: 1976

Answers (2)

Nitesh Halai
Nitesh Halai

Reputation: 927

You need to double all the double quotes used in the formula. e.g. "" would become """" and if you have something like "Yes" in the formula, it would become ""Yes"".

Range("G24:G47").Formula = "=IF(F24="""","""",G24*0.14)"

Upvotes: 2

Hyrein
Hyrein

Reputation: 391

You can just straight ignore the NO like this :

If VAT = "YES" Then
Range("G24:G47").Formula = "=IF(F24="","",G24*0.14)"
Else
Range("G24:G47").Formula = "=IF(F24="","",G24*0)"
End If

otherwise 

If VAT = "YES" Then
Range("G24:G47").Formula = "=IF(F24="","",G24*0.14)"
ElseIf VAT = "NO" Then
Range("G24:G47").Formula = "=IF(F24="","",G24*0)"
Else
 Exit Sub
End If

Whatever type of "code block" you're currently in, you have "Exit" the same kind, so "Exit Sub", "Exit Function", etc.

Upvotes: 0

Related Questions