Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup - column index number - create as variable
I'm writing the following code to fill spreadsheets with
vlookup formulas. I can get the formula to be put in the rows down & across that I need to fill, but what I want to be able to do is create a variable so that the col_index_num in the vlookup changes as the formula is placed into that colum. variable = 1 to 10 eg - =vlookup(a1,a5:f10,PUT VARIABLE HERE,0) next variable is this possible ???? Sub Macro20() ' ' Macro20 Macro ' Macro recorded 5/08/2004 by John Young Dim rowcount As Integer Dim columncount As Integer Dim cellcountdown As Integer Dim cellcountacross As Integer Sheets("g419015_2_lf6_intersect_summari").Activate Range("a2", Range("a2").End(xlDown)).Select rowcount = Selection.Rows.Count Range("g1", Range("g1").End(xlToRight)).Select columncount = Selection.Columns.Count For cellcountdown = 2 To (rowcount + 1) For cellcountacross = 1 To columncount 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[-1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,RC[1],FALSE)" Next cellcountacross Next cellcountdown Range("A2").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup - column index number - create as variable
=vlookup(a1,a5:f10,column(A1),0)
-- Regards, Tom Ogilvy "john young" wrote in message ... I'm writing the following code to fill spreadsheets with vlookup formulas. I can get the formula to be put in the rows down & across that I need to fill, but what I want to be able to do is create a variable so that the col_index_num in the vlookup changes as the formula is placed into that colum. variable = 1 to 10 eg - =vlookup(a1,a5:f10,PUT VARIABLE HERE,0) next variable is this possible ???? Sub Macro20() ' ' Macro20 Macro ' Macro recorded 5/08/2004 by John Young Dim rowcount As Integer Dim columncount As Integer Dim cellcountdown As Integer Dim cellcountacross As Integer Sheets("g419015_2_lf6_intersect_summari").Activate Range("a2", Range("a2").End(xlDown)).Select rowcount = Selection.Rows.Count Range("g1", Range("g1").End(xlToRight)).Select columncount = Selection.Columns.Count For cellcountdown = 2 To (rowcount + 1) For cellcountacross = 1 To columncount 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[-1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,RC[1],FALSE)" Next cellcountacross Next cellcountdown Range("A2").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup - column index number - create as variable
Sorry, didn't see you code down below. I won't try to get inside that, but
for the simple example at the top for variable = 1 to 10 cells(variable,1).Formula = "=vlookup(Sheet3!a1,Sheet3!a5:j10," & _ Variable & ",0)" next variable your look up range has to have at least as many columns as the nth column you use for the 3rd argument. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... =vlookup(a1,a5:f10,column(A1),0) -- Regards, Tom Ogilvy "john young" wrote in message ... I'm writing the following code to fill spreadsheets with vlookup formulas. I can get the formula to be put in the rows down & across that I need to fill, but what I want to be able to do is create a variable so that the col_index_num in the vlookup changes as the formula is placed into that colum. variable = 1 to 10 eg - =vlookup(a1,a5:f10,PUT VARIABLE HERE,0) next variable is this possible ???? Sub Macro20() ' ' Macro20 Macro ' Macro recorded 5/08/2004 by John Young Dim rowcount As Integer Dim columncount As Integer Dim cellcountdown As Integer Dim cellcountacross As Integer Sheets("g419015_2_lf6_intersect_summari").Activate Range("a2", Range("a2").End(xlDown)).Select rowcount = Selection.Rows.Count Range("g1", Range("g1").End(xlToRight)).Select columncount = Selection.Columns.Count For cellcountdown = 2 To (rowcount + 1) For cellcountacross = 1 To columncount 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" 'Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[- 1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,2,FALSE)" Cells(cellcountdown, (cellcountacross + 6)).VALUE = "=VLOOKUP(RC[-1],'[BHSTStateRegister_TESTJY.xls]Broad Hydrological Soil Types'!R2C1:R201C63,RC[1],FALSE)" Next cellcountacross Next cellcountdown Range("A2").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I automatically update column index number in VLookup whe. | Excel Worksheet Functions | |||
VLOOKUP Column Index | Excel Discussion (Misc queries) | |||
vlookup column index number argument | Excel Worksheet Functions | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
Multiple Column Index Number in VLookup | Excel Worksheet Functions |