Quick Formula Question

WorkMC

Board Regular
Joined
Jul 21, 2005
Messages
88
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))"
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

WorkMC

Board Regular
Joined
Jul 21, 2005
Messages
88
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Try sticking a space or two around the &, VBA can be a bit picky about that.
Code:
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R[1]C[-3]:R" & LastRow & "C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"
 

WorkMC

Board Regular
Joined
Jul 21, 2005
Messages
88
Norie, that solved it.

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

Watch MrExcel Video

Forum statistics

Threads
1,118,540
Messages
5,572,791
Members
412,484
Latest member
deezina07
Top