Quick Formula Question

[WorkMC]

I'm trying to use LastRow = Range("A65536").End(xlUp).Row to replace the 20000 in the formula below. When I do the formula appears in red, obviously I'm missing something can anyone help.

This is the current formula

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R[1]C[-3]:R20000C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"

This is how I've tried to adjust it:

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R[1]C[-3]:R"&LastRow&"C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"

 

[WorkMC]

This is what I usually use to get the LastRow

Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row

Normally I then adjust my formulas to include LastRow, but for some reason in my earlier attempt it does not work and I don't understand why?

 

[Norie]

Try sticking a space or two around the &, VBA can be a bit picky about that.

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R[1]C[-3]:R" & LastRow & "C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"

 

[WorkMC]

Norie, that solved it.

Thanks I could'nt see the wood for the trees.