Code works on sheet but not from code-to-sheet

[Howard]

The first line of code works fine.
The commented out line to return "" produces error 400.

Also, is it possible to do the "value = value" on the commented out code so there are no formulas in A1:A5, just the returned values?

Thanks,
Howard

Option Explicit
Sub X()
Range("A1:A5").Formula = "=VLOOKUP(C1, $D$1:$F$5, 3, 0)"

'Formula here works in sheet but not by code
'Range("A1:A5").Formula = "=IF(VLOOKUP(C1,$D$1:$F$5,3,0)=0,"",(VLOOKUP(C1,$D$1:$F$5,3,0)))"

End Sub

 

[Ben McClave]

Howard,

Try this:

Sub X()

With Range("A1:A5")
.Formula = "=IF(VLOOKUP(C1,$D$1:$F$5,3,0)=0,"""", VLOOKUP(C1,$D$1:$F$5,3,0)))"
.Value = .Value
End With

End Sub

 

[Howard]

Howard,



Try this:



Sub X()



With Range("A1:A5")

.Formula = "=IF(VLOOKUP(C1,$D$1:$F$5,3,0)=0,"""", VLOOKUP(C1,$D$1:$F$5,3,0)))"

.Value = .Value

End With



End Sub

Thanks, Ben. Works smack on, just had to add the missing "(".

.Formula = "=IF(VLOOKUP(C1,$D$1:$F$5,3,0)=0,"""",(VLOOKUP(C1,$D$1:$F$5,3,0)))"

Regards,
Howard