Evaluate VLOOKUP in cells in column
in [Programming]
|
Prev: VBA source or reference
Next: Save as vba excel
From: Bob Phillips on 29 Apr 2010 06:34 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" <bobbell(a)discussions.microsoft.com> wrote in message news:548AD5DB-B5F3-4203-B585-FDE07E1EC5B1(a)microsoft.com... > 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" <bobbell(a)discussions.microsoft.com> wrote in message >> news:051F410B-78AF-46DE-BB62-1ABEFFE082A1(a)microsoft.com... >> > 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 >> >> >> >> >> >> >> . >>
From: Robert on 29 Apr 2010 07:25
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" <bobbell(a)discussions.microsoft.com> wrote in message > news:548AD5DB-B5F3-4203-B585-FDE07E1EC5B1(a)microsoft.com... > > 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" <bobbell(a)discussions.microsoft.com> wrote in message > >> news:051F410B-78AF-46DE-BB62-1ABEFFE082A1(a)microsoft.com... > >> > 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 > >> >> > >> >> > >> > >> > >> . > >> > > > . > |