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
.
|