Evaluate VLOOKUP in cells in column

[Robert]

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

 

[ozgrid.com]

Try along the lines of

Range(Cells(1, 1),Cells(Rows.Count).End(xlUp)) _
.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"

 

[Robert]

Sorry, does not work. I tried to figure it out but with no success. Moreover,
the code writes the formulas not the values.
--
Robert

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

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

 

[Bob Phillips]

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With


--

HTH

Bob

Sorry, does not work. I tried to figure it out but with no success.
Moreover,
the code writes the formulas not the values.
--
Robert

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

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

 

[ozgrid.com]

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

Sorry, does not work. I tried to figure it out but with no success.
Moreover,
the code writes the formulas not the values.
--
Robert

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

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

 

[Robert]

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]

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

 

[Bob Phillips]

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

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

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With

 

[Robert]

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

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

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

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With

.

 

[Robert]

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

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

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

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With

.

 

[Bob Phillips]

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

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

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

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


:

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With

.

 

[Robert]

Thank you Bob it is working exactly as I desired.
--
Robert

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

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

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

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


:

With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

.FormulaR1C1 = "=VLOOKUP(RC[4],AgeGroup,2)"
.Value = .Value
End With


--

HTH




.

.