Vlookup with varing Column length

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matadi Tappe - 24 Nov 2007 04:52 GMT

I have this code:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC37,'PRICING TEMPLATE II .xls'!Cat_Code,2,0)"
Used Macro recorder and it works to an extent. I have to manually copy and
paste to the last text entry in cloumn AD. I can not modify code to go as
far as the last occupied cell in cloumn AD and stop. Any sugestion as to the
routine to use for this. The Answer is returned to Cell A2 on down.

Thanks

Signature

Tappita

Reply to this Message

Joel - 24 Nov 2007 09:16 GMT

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC37,'PRICING TEMPLATE II .xls'!Cat_Code,2,0)"
LastRow = Cells(Rows.Count, 37).End(xlUp).Row

ActiveCell.Copy _
Destination:=Range(ActiveCell, Cells(LastRow, ActiveCell.Column))

> I have this code:
> ActiveCell.FormulaR1C1 = _
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Matadi Tappe - 24 Nov 2007 13:02 GMT

You are an expert. It works. Thanks Joel!

Signature

Tappita

> ActiveCell.FormulaR1C1 = _
> "=VLOOKUP(RC37,'PRICING TEMPLATE II .xls'!Cat_Code,2,0)"
[quoted text clipped - 12 lines]
> >
> > Thanks

Reply to this Message