Evaluate VLOOKUP in cells in column
in [Programming]
|
Prev: VBA source or reference
Next: Save as vba excel
From: Robert on 28 Apr 2010 06:50 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
From: Robert on 29 Apr 2010 03:35 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: Bob Phillips on 29 Apr 2010 03:51 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 04:54 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" <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 05:09
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 > >> > >> > > > . > |