Vlookup macro

[Rick]

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

 

[OssieMac]

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)"

 

[Rick]

OSSIEMAC

Below is the code I tried to use to select the first and last cells in the
range. Unfortunately I don't know what they will be from time to time.

Range("E3").Select


Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.End(xlDown).Offset(1, -4)
Set LastdataCell = FirstdataCell.End(xlDown).Offset(0, 5)

Range("e3") = "=VLOOKUP(A3,firstcell:lastcell,5,FALSE)"


This is what was pasted into E3, =VLOOKUP(A3,firstcell:lastcell,5,FALSE) but
isn't giving the expected result.

Any ideas?

Thanks
Rick

 

[OssieMac]

My apologies if this gets posted twice. It seemed to fail the first time.

Hi Rick,

I am assuming that the lookup range sits below the rows where you are
inserting the formula. Is this correct? It looks like the code starts from
Range E3 and then down past empty cells until it finds data and then left to
column A and this becomes firstDataCell. The code then goes to the bottom of
the data in column A and across to column E and this becomes lastDataCell.

You can not use VBA variables firstDataCell and lastDataCell in formulas
which are being inserted into the worksheet. The values of these variables
are lost when the code finishes.

The variables I have used in the formula are names which can be inserted
manually by the user on the worksheet although I have inserted them using VBA
code. To insert the names manually, if using xl2007 then you select Formulas
ribbon and then Define Names. If using earlier versions of xl then select
menu item Insert->Name->Define and then these defined names can be used in
lieu of the cell or range address in the formula. Note you can name
individual cells or ranges.

Anyway if you are identifying the first and last cell correctly, your code
should be like this. Note the comments. Also you might already know this but
a space and underscore at the end of a line is a line break in an otherwise a
single line of code.

Sub testVlookup()
Dim headerCell As Range
Dim lastDataCell As Range
Dim firstDataCell As Range

'Range("E3").Select 'No need to select the cell

With Sheets("Sheet1")
Set headerCell = .Range("E3")

'this is a cell in column A
Set firstDataCell = headerCell.End(xlDown) _
.Offset(1, -4)

'this is a cell in column F
Set lastDataCell = firstDataCell.End(xlDown) _
.Offset(0, 5)

'the msgboxes will confirm if you have the correct
'first and last cells of the range and you can delete
'them when finished
MsgBox headerCell.Address
MsgBox firstDataCell.Address
MsgBox lastDataCell.Address

ActiveWorkbook.Names.Add Name:="FirstCell", _
RefersToR1C1:=firstDataCell
ActiveWorkbook.Names.Add Name:="LastCell", _
RefersToR1C1:=lastDataCell

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

'alternative method is to name the entire lookup range
ActiveWorkbook.Names.Add Name:="LookupRange", _
RefersToR1C1:=.Range(firstDataCell, lastDataCell)

.Range("E3") = "=VLOOKUP(A3,LookupRange,5,FALSE)"

End With
End Sub