Cells Within Range Failure?

[Bob Zimski]

I don't know why, but the compiler is protesting the second to last line and
I don't know why. What have I missed?

Thanks

For i = maxrows To 2 Step -1
FormulaStr =
"=IF(ISNA(VLOOKUP(cells(i,1),[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE)),""x"",VLOOKUP(cells(i,1),[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE))"
Range(Cells(i, 5)).Formula = FormulaStr
Next i

 

[Mike H]

Bob,

There was also an error in your Vlookup because you had
cells(i,1) inside the quotes it was treated as text and you wanted a
variable. try this instead

For i = maxrows To 2 Step -1
FormulaStr = "=IF(ISNA(VLOOKUP(" & Cells(i, 1).Address &
",[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE)),""x"",VLOOKUP(" & Cells(i,
1).Address & ",[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE))"
Cells(i, 5).Formula = FormulaStr
Next i


Mike

 

[Jim Thomlinson]

What you have will not produce a valid formula. In your string you have
cells(i,1)
which is not going to be evalueated to a value. It will be exactly what you
see.

"=IF(ISNA(VLOOKUP(" & cells(i,1) &
",[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE)),""x"",VLOOKUP(" &
cells(i,1) & ",[NegsExceptions.xls]Sheet1!$A$1:$C$18,1,FALSE))"

Assuming all else to be correct