nested if vba problem

[leitek.com]

Hi there,

I am trying to create a nested if that look at column N and determine
the month number, then add the lookup function below based on month
number in column n. I am sure there is an easier way to do this. I
think I need to use a loop but I am not sure how it works.


Column N Column O
10 "=+VLOOKUP(RC[-10],Jan,2,FALSE)"
11
12
1
12

Sub test()

' lookup function based on left column value

'Do

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

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

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

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

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

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

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

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

Else
If ActiveCell.Offset(0, -1) = 12 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Dec,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

your help is greatly appreciated.

thanks

 

[Bob Phillips]

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)"

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

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

 

[Bob Phillips]

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)