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

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

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

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

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

On Tuesday, June 4, 2013 7:29:10 PM UTC-7, Ben McClave wrote:
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