There are a couple of ways of doing this but it is dependant on how you will
identify the first cell and last cell of the lookup range when it is a
variable range. Do you know how to do this and if so can you post the code
you propose using to do it?

If not, what can you tell me about the lookup range that will always be
constant like any of the following
The starting cell. If so, what is it?
The number of columns
Any columns and rows that always have data for the full length of the range.
(That is no empty cells)
Any rows that will always have data for the full witdth of the or range.
(That is no empty cells)

The following code is one sample of how the code can be written if you can
identify the first and last cell in the range. (In the real code I would try
to avoid actually selecting cells if possible.)

Note that formulas can be entered into cells using VBA by simply assigning a
string to the cell. (It is not necessary to use the format that you get if
you try to record the code.)

Range("G4").Select 'Identify the first cell in the range
ActiveWorkbook.Names.Add Name:="FirstCell", RefersToR1C1:=ActiveCell

Range("L28").Select 'Identify the last cell in the range
ActiveWorkbook.Names.Add Name:="LastCell", RefersToR1C1:=ActiveCell

Range("E3") = "=VLOOKUP(A3,FirstCell:LastCell,5,FALSE)"


--
Regards,

OssieMac


"Rick" wrote:

I am trying to automate the formatting of a spreadsheet into a new format.

I nee to add a vlookup into a range that is going to change each time I run
it.

The formula in cell E3 will look like this -
=vlookup(A3,A500:Ee3000,5,false), where the 500:3000 is actually unknown each
month.

Once the formula is written it need sot be copied down to cell E499, or
whatever that cell will be but I got that covered. I just need know how to
have the data range by dynamic.

I tried using firstdatacell and lastdatacell but couldn't figure out how to
work that into the Vlookup formula.

Also one quick reminder.

When I want to go down then across a couple of rows I tried

activecell.end(xldown).offset(0,2).select

but it isn't working have I got something wrong?

Thanks in advance
Rick