Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro
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 -- Regards, OssieMac -- Regards, OssieMac "Rick" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Macro | Excel Discussion (Misc queries) | |||
Vlookup Macro? | Excel Discussion (Misc queries) | |||
vlookup macro | New Users to Excel | |||
VLOOKUP in a macro?? | Excel Discussion (Misc queries) | |||
Please help.. VLookup Macro | Excel Programming |