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

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

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