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

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

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

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

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

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

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

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

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




.

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




.

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




.

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




.



.