VBA and IF Statement

[Ike Hunter]

Hi,

Need help with VBA and IF Statement

Example1: Works fine with VBA with the exception of "#VALUE!" showing in the B column when cell in column A is blank.

Example2: IF Statement... I can copy the formula from notepad and paste in cell B2 or type it in and the formula works properly.
However, in VBA ... Run-time error '1004': Application-defined or object-defined error.

'Example1 ... WS.Range("B2").Formula = "=RIGHT(A2,LEN(A2)-22)"

'Example2 ... WS.Range("B2").Formula = "=IF(A2="",(""),RIGHT(A2,LEN(A2)-22))"


This is how I am using the above...

Dim WS As Worksheet

Set WS = Worksheets("Temp")
Range("A2:A100").NumberFormat = "@"
WS.Range("B2").Formula = "=IF(A2="",(""),RIGHT(A2,LEN(A2)-22))"

Range("B2").Copy
Range("B3:B100").PasteSpecial xlPasteFormulas

Dim myApp As String
myApp = Shell("Notepad", vbNormalFocus)


I thank you in advance.

 

[Joe4]

When you try to put literal double-quotes in your formula via VBA, you need to double-up all those quotes, as double-quotes are treated as text qualifiers.
I often find that the formulas can be quote confusing. The good news is that you do not need to figure it out yourself, let Excel do it for you!

You can do this by turning on the Macro Recorder and record yourself entering the formula into your desired cell. Then stop the Macro Recorder and view the code.
Copy the formula portion of that code to where you need it in your code, and you should be all set!

 

[Ike Hunter]

I thank you for the education on that. As soon as I read your solution I recognized exactly what you were conveying. I added the extra quotes. The formula works perfect now.
"=IF(A2="""",(""""),RIGHT(A2,LEN(A2)-22))"

 

[Joe4]

You are welcome.
Glad I was able to help!