Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > nested if vba problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default nested if vba problem
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default nested if vba problem
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" wrote in message
ups.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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default nested if vba problem
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..

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default nested if vba problem
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..



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF nested problem ..! Peter[_14_] Excel Discussion (Misc queries) 2 April 11th 10 01:00 PM
A problem with Nested IFs The Narcissist Excel Worksheet Functions 0 January 23rd 08 12:12 AM
Nested IF problem Andrew Mackenzie Excel Discussion (Misc queries) 3 June 28th 07 12:20 PM
Nested if problem samonly Excel Worksheet Functions 1 March 14th 06 01:33 PM
Nested IF problem - help please Dasin Excel Worksheet Functions 11 November 22nd 05 09:23 PM


All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement