Ike Hunter
New Member
- Joined
- Dec 15, 2011
- Messages
- 35
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.
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.