Results 1 to 10 of 10

Thread: New to VBA - Basic Macro Help Needed

  1. #1
    Seeker Peg's Avatar
    Join Date
    May 2021
    Posts
    5
    Articles
    0
    Excel Version
    Windows 10

    Question New to VBA - Basic Macro Help Needed



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I am new to VBA and this forum. I am hoping someone has a moment to help me?

    The macro, below, is executed by clicking one of three choices in a combobox. The combobox is linked to cell "L5". Each time one of the options (Turn, River, Other) is chosen, the result in cell "O14 is the same...the value from the last line executed under "Else".
    I have tested the "Index" functions (one at a time) in the Master sheet formula bar, and the results were correct for each. Your time and assistance is appreciated.

    Code:
    Sub StreetChoice()
    
    
     'To match street choice to options
     
        If Range("L5") = "Turn" Then
            Range("O14").Value = "= Index(G58:G80,match(I6,E58:E80,0))"
     
        ElseIf Range("L5") = "River" Then
            Range("O14").Value = "= Index(H58:H80,match(I6,E58:E80,0))"
        
        Else
            Range("O14").Value = "= Index(I58:I80,match(I6,E58:E80,0))"
     
        End If
    
    
    End Sub
    Last edited by p45cal; 2021-05-12 at 10:06 AM. Reason: added code tags

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Let's say you are using Sheet 1 for this macro.

    Paste the macro into the Sheet1 Module :

    Code:
    Option Explicit
    
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'To match street choice to options
    
    
        If Range("L5") = "Turn" Then
        Range("O14").Value = "= Index(G58:G80,match(I6,E58:E80,0))"
        
        ElseIf Range("L5") = "River" Then
        Range("O14").Value = "= Index(H58:H80,match(I6,E58:E80,0))"
        
        Else
        Range("O14").Value = "= Index(I58:I80,match(I6,E58:E80,0))"
        
        End If
        
    End Sub
    Not certain what data your formulas are referencing so I could not fully test the macro.

  3. #3
    Seeker Peg's Avatar
    Join Date
    May 2021
    Posts
    5
    Articles
    0
    Excel Version
    Windows 10
    Thank you, Logit. I pasted the macro into the "Master" sheet module, as a selection change, where the macro is being used with still the same "Else" result. Any other ideas as to why it is not "seeing" the other options?

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Please post your workbook for review. You will need to click on the GO ADVANCED button, lower right corner of the REPLY window.

  5. #5
    Seeker Peg's Avatar
    Join Date
    May 2021
    Posts
    5
    Articles
    0
    Excel Version
    Windows 10
    Quote Originally Posted by Logit View Post
    Please post your workbook for review. You will need to click on the GO ADVANCED button, lower right corner of the REPLY window.
    I am, unfortunately, not able to do that as it is related to proprietary information. Thank you for following-up.

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Create a separate workbook with only the immediate information you are referencing in this thread. Do not include any sensitive data. Create some "dummy data".

  7. #7
    Seeker Peg's Avatar
    Join Date
    May 2021
    Posts
    5
    Articles
    0
    Excel Version
    Windows 10
    Great idea! Thanks! I completed the dummy workbook, and well...everything worked perfectly in it. So the code works, but not in the original version. Hmmm. I think I will re-enter all the referenced data in the original version and try it again. Thank you, again, for your time and knowledge.

  8. #8
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Ok. Glad the dummy workbook worked for you. Hopefully you can figure out why the original is not functioning.

  9. #9
    Seeker Peg's Avatar
    Join Date
    May 2021
    Posts
    5
    Articles
    0
    Excel Version
    Windows 10
    This is a re-post with an update. I created the dummy workbook, and the code worked perfectly. So the issue was somewhere in the referenced data within the original version. Finally, I discovered the problem, but not sure why it made a difference. I re-typed the table containing, "Turn, River, Other" exactly as before and then tried executing the macro. Boom! It worked! Thank you, again, for all your efforts and making my first forum debut query a success!!!

  10. #10
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Excel is known for corrupting data without notice. Also, if you've tried running a macro several times and it crashes each time ... that can sometimes corrupt data.

    When the macro crashes, I've found always clicking on DEBUG instead of END in the error message window helps Excel to not corrupt the file so soon.

    Cheers !

Posting Permissions

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