Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Using Excel2003, I have copyied and modified the following code which
essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just Cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge. Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Try along the lines of
Range(Cells(1, 1),Cells(Rows.Count).End(xlUp)) _ .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" -- Regards Dave Hawley www.ozgrid.com "Robert" wrote in message ... Using Excel2003, I have copyied and modified the following code which essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just Cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge. Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Sorry, does not work. I tried to figure it out but with no success. Moreover,
the code writes the formulas not the values. -- Robert "ozgrid.com" wrote: Try along the lines of Range(Cells(1, 1),Cells(Rows.Count).End(xlUp)) _ .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" -- Regards Dave Hawley www.ozgrid.com "Robert" wrote in message ... Using Excel2003, I have copyied and modified the following code which essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH Bob "Robert" wrote in message ... Sorry, does not work. I tried to figure it out but with no success. Moreover, the code writes the formulas not the values. -- Robert "ozgrid.com" wrote: Try along the lines of Range(Cells(1, 1),Cells(Rows.Count).End(xlUp)) _ .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" -- Regards Dave Hawley www.ozgrid.com "Robert" wrote in message ... Using Excel2003, I have copyied and modified the following code which essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Moreover,
the code writes the formulas not the values. It doesn't for me, you have broken it by trying to adapt it. What range do you your VLOOKUPS in?? -- Regards Dave Hawley www.ozgrid.com "Robert" wrote in message ... Sorry, does not work. I tried to figure it out but with no success. Moreover, the code writes the formulas not the values. -- Robert "ozgrid.com" wrote: Try along the lines of Range(Cells(1, 1),Cells(Rows.Count).End(xlUp)) _ .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" -- Regards Dave Hawley www.ozgrid.com "Robert" wrote in message ... Using Excel2003, I have copyied and modified the following code which essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
My code. Any response will only be seen by me many hours later.
Sub AgeGroup() Range(Cells(1, 1), Cells(Rows.Count).End(xlUp)) _ .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" End Sub -- Robert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Bob, Thanks. Maybe my original post was not clear.
After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Do you mean?
Sub AgeGroup2() With Range(Cells(2, "F"), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- HTH Bob "Robert" wrote in message ... Bob, Thanks. Maybe my original post was not clear. After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
What a pain I am. It's not working. Could it have anthing to to with Resize
given R1C1 is used. The earlier code works with the correct answer except that it is written in Col A instead of Col F. Is there a way to amend my first code so that the anwers are converted to Values -- Robert "Bob Phillips" wrote: Do you mean? Sub AgeGroup2() With Range(Cells(2, "F"), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- HTH Bob "Robert" wrote in message ... Bob, Thanks. Maybe my original post was not clear. After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Bob, I just amended my original code as below. It works except that the
numbers are not "values" Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = "=VLOOKUP(RC[-1],AgeGroup,2)" Next i Next j End Sub I know there are redundancies but that is the only way "I know". -- Robert "Bob Phillips" wrote: Do you mean? Sub AgeGroup2() With Range(Cells(2, "F"), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- HTH Bob "Robert" wrote in message ... Bob, Thanks. Maybe my original post was not clear. After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Sub AgeGroup()
Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For i = 2 To 64501 Step 1 With Cells(i, "F") .FormulaR1C1 = "=VLOOKUP(RC[-1],AgeGroup,2)" .Value = .Value End With Next i End Sub -- HTH Bob "Robert" wrote in message ... Bob, I just amended my original code as below. It works except that the numbers are not "values" Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = "=VLOOKUP(RC[-1],AgeGroup,2)" Next i Next j End Sub I know there are redundancies but that is the only way "I know". -- Robert "Bob Phillips" wrote: Do you mean? Sub AgeGroup2() With Range(Cells(2, "F"), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- HTH Bob "Robert" wrote in message ... Bob, Thanks. Maybe my original post was not clear. After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate VLOOKUP in cells in column
Thank you Bob it is working exactly as I desired.
-- Robert "Bob Phillips" wrote: Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For i = 2 To 64501 Step 1 With Cells(i, "F") .FormulaR1C1 = "=VLOOKUP(RC[-1],AgeGroup,2)" .Value = .Value End With Next i End Sub -- HTH Bob "Robert" wrote in message ... Bob, I just amended my original code as below. It works except that the numbers are not "values" Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = "=VLOOKUP(RC[-1],AgeGroup,2)" Next i Next j End Sub I know there are redundancies but that is the only way "I know". -- Robert "Bob Phillips" wrote: Do you mean? Sub AgeGroup2() With Range(Cells(2, "F"), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- HTH Bob "Robert" wrote in message ... Bob, Thanks. Maybe my original post was not clear. After toying for several hours, I had to amend (Cells(1,1) to (Cells(2, 1). It writes as I intended but in Col 1 (A) instead fo Col 6(F). I cannot figure how to. Your assistance please. My amended code below. Sub AgeGroup2() With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With End Sub -- Robert "Bob Phillips" wrote: With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to Evaluate Range and Sum Different Column | Excel Discussion (Misc queries) | |||
Counting cells using EVALUATE | Excel Discussion (Misc queries) | |||
evaluate contents and select correct column | Excel Programming | |||
Evaluate a column and extract last value | Excel Worksheet Functions | |||
VB evaluate a value in a table's column and display msgbox | Excel Discussion (Misc queries) |