# Quick Formula Question

#### WorkMC

##### Board Regular
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(RC[-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(RC[-3]:R"&LastRow&"C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"

### 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
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
Try sticking a space or two around the &, VBA can be a bit picky about that.
Code:
``````ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-3]:R" & LastRow & "C1,RC[-3]),""x"",VLOOKUP(RC[-3],R2C1:RC[-2],2,0))"``````

#### WorkMC

##### Board Regular
Norie, that solved it.

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

Replies
4
Views
178
Replies
2
Views
45
Replies
1
Views
111
Replies
8
Views
270
Replies
7
Views
217