But you are not looping through anything, all you had was a series of Ifs to
see which month to use. I replaced that with a single lookup method, saving
the tests.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"leitek.com" wrote in message
oups.com...
Bob,

thanks for the reply. sure this looks much more efficient but I was
originally asking for how to loop this through the selected cells to
make column O determine which month to lookup based on the month number
in cell N.

I replaced my code with your lines which now look like the following:


Sub test()

' lookup function based on left column value

'Do
aryMonths = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

If Selection.Offset(0, -1) = 1 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 2 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 3 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"
Else
If Selection.Offset(0, -1) = 4 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 5 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"
Else
If Selection.Offset(0, -1) = 6 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 7 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 8 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"
Else
If ActiveCell.Offset(0, -1) = 9 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 10 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If Selection.Offset(0, -1) = 11 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 12 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
'Loop Until Range("N:N") = True


End Sub

Any Ideas...!!

Thanks again for your help..