nested if vba problem

leitek.com · 02-15-2006, 02:35 PM

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 · 02-15-2006, 03:00 PM

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" <leitek@gmail.com> wrote in message
news:1140028176.782879.24600@g47g2000cwa.googlegroups.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
>

leitek.com · 02-15-2006, 04:10 PM

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 · 02-15-2006, 05:15 PM

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" <leitek@gmail.com> wrote in message
news:1140033778.117221.239760@f14g2000cwb.googlegroups.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..
>