Evaluate VLOOKUP in cells in column
in [Programming]
|
Prev: VBA source or reference
Next: Save as vba excel
From: Robert on 28 Apr 2010 03:59 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
From: ozgrid.com on 28 Apr 2010 04:21 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" <bobbell(a)discussions.microsoft.com> wrote in message news:CC39204E-FB61-424E-AE01-C81EFE55F80F(a)microsoft.com... > 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
From: Robert on 28 Apr 2010 05:38 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" <bobbell(a)discussions.microsoft.com> wrote in message > news:CC39204E-FB61-424E-AE01-C81EFE55F80F(a)microsoft.com... > > 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 >
From: Bob Phillips on 28 Apr 2010 05:54 With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) .FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)" .Value = .Value End With -- HTH Bob "Robert" <bobbell(a)discussions.microsoft.com> wrote in message news:35462154-0873-4CD5-A18B-9D9150F07DB8(a)microsoft.com... > 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" <bobbell(a)discussions.microsoft.com> wrote in message >> news:CC39204E-FB61-424E-AE01-C81EFE55F80F(a)microsoft.com... >> > 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 >>
From: ozgrid.com on 28 Apr 2010 05:58
RE: 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" <bobbell(a)discussions.microsoft.com> wrote in message news:35462154-0873-4CD5-A18B-9D9150F07DB8(a)microsoft.com... > 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" <bobbell(a)discussions.microsoft.com> wrote in message >> news:CC39204E-FB61-424E-AE01-C81EFE55F80F(a)microsoft.com... >> > 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 >> |