+ Reply to Thread
Results 1 to 12 of 12

More Than 7 Nested If -Then - Else through VBA

  1. #1
    Registered User
    Join Date
    12-10-2009
    Location
    New City, NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    More Than 7 Nested If -Then - Else through VBA

    Hello, I'm trying to overcome Excel's 7-Nest limit through my first VBA program. My objective is to replace a text string with a numeric variable. The string has text values such as US, Canada, Germany, France etc, There are about 15 such words in total. Each row will have a different one of the 15 text strings.

    Each text string has a corresponding number, which I want to use in various formulas throughout the spreadsheet. The numbers are located on a separate worksheet (“Match Rate”), within the same workbook. I obtained the numbers from a formatted report, which I simply pasted into the second worksheet. Due to the complexity of the report, I can’t reformat the numbers to allow use of the VLOOKUP function.

    I created the following function, but unfortunately it doesn’t work. Can you help, please?

    I’d like to stay with a VBA solution, since a simple if/then sequence such as this is an excellent introduction to the powerful world of VBA... (it just doesn’t work, yet). I suspect the problem is in the calls to the Match Rate Worksheet.

    Thank you in advance!


    Please Login or Register  to view this content.
    Last edited by LKluger; 12-11-2009 at 10:29 AM. Reason: Clarification

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Help with Nested If in VBA

    Hi,
    welcome to the forum. Please, read forum rules first. Please, use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Help with Nested If in VBA

    Thanks for editing your post.
    You can use multiple If..End If statements, instead of single If..ElseIf..End If
    Or, which is better solution - use Select Case statement.

  4. #4
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Help with Nested If in VBA

    In fact the problem is not with the nested If. It is the way you reference the cells. Here is the correct code with If..ElseIf..EndIf statement
    Please Login or Register  to view this content.
    Sorry, that I overlooked that in my first responce.
    Last edited by buran; 12-11-2009 at 02:09 AM. Reason: clarification

  5. #5
    Registered User
    Join Date
    12-10-2009
    Location
    New City, NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Help with Nested If in VBA

    Thanks very much, Buran, for the ideas. They look like more elegant code. I'll experiment with them in the future.

    And thanks so much for the fix! I plugged it in and it works perfectly! I was thrown for a bit since my first test example was lowercase; I didn't realize at first that the text string is case sensitive.

    Have a good morning...

    - LK
    Last edited by LKluger; 12-11-2009 at 02:33 AM.

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Help with Nested If in VBA

    If I may suggest another idea - you can also use names. Define named range for each country rate using Name Manger and use this name in formulas.
    I don't know if this will fit your needs, but is a good alternative to custom function for what you a trying to do.

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Help with Nested If in VBA

    It looks like a VLOOKUP table could be used instead of code.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  8. #8
    Registered User
    Join Date
    12-10-2009
    Location
    New City, NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Help with Nested If in VBA

    Great point, Mike. In my actual spreadsheet, VLOOKUP would not work since the figures I'm accessing are in various cells all over the place -- due to the format of the report I imported.

    I created a simple list of numbers for my example, so I could test the programming and work out the bugs.

    Thanks again for the suggestion.

  9. #9
    Registered User
    Join Date
    12-10-2009
    Location
    New City, NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Help with Nested If in VBA

    Buran, using Named ranges works well in other formulas in my spreadsheet -- I'm not sure it would work in this case.

    I'm using the spreadsheet as a simple database, with a few hundred rows of data. Each row has a different Geo indicator, so the formulas will differ based on which Geo applies.

    I'll try a few more options tomorrow -- and thanks again for all your help!

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Help with Nested If in VBA

    Select case would be a better VBA solution
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  11. #11
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Help with Nested If in VBA

    Because I also think that Select Case statement is better (and pointed this in my first post), here is the code:
    Please Login or Register  to view this content.
    By the way, there is error in my first code

    Please Login or Register  to view this content.
    should be

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-10-2009
    Location
    New City, NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Help with Nested If in VBA

    RoyUK and Buran, thank you for the Select Case advice. And Buran, I really appreciate your going to the trouble of re-editing the sample to show how it works. I'll now use the Select Case technique in my spreadsheet.

    Before I posted, I searched the archives for a similar example, but didn't find anything even after going back a few years. So hopefully this will be helpful to other users...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1