adding a variable to a formula

[LGMan]

Hi,
This forum has been kind to me before, so hope I can get some pointers on this.
I have a formula which applies a rank to the data, however at the moment I have to hardcode the data range.
for example..

Range("G5").Value = _
"=RANK(F5,OFFSET($C$4,MATCH(C5,$C$5:$C$544,0),3,COUNTIF($C$5:$C$544,C5),1),0)"
I manually update the end of the range e.g C$544.

I have a Lastrow variable which I use elsewhere and I'd like to use in this formula , but can't get the syntax right in my formaula as the code just bombs.

any help most appreciated

 

[PHV]

Range("G5").Formula = _
"=RANK(F5,OFFSET($C$4,MATCH(C5,$C$5:$C$" & LastRow & ",0),3,COUNTIF($C$5:$C$" & LastRow & ",C5),1),0)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LGMan]

Thanks PHV,
I was missing the & after Lastrow.
Cheer