New to VBA - Basic Macro Help Needed

P

Peg

New member
Joined
May 10, 2021
Messages
5
Reaction score
0
Points
0
Excel Version(s)
Windows 10
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 a moderator:
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.
 
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?
 
Please post your workbook for review. You will need to click on the GO ADVANCED button, lower right corner of the REPLY window.
 
Logit said:
Please post your workbook for review. You will need to click on the GO ADVANCED button, lower right corner of the REPLY window.
Click to expand...

I am, unfortunately, not able to do that as it is related to proprietary information. Thank you for following-up.
 
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".
 
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.
 
Ok. Glad the dummy workbook worked for you. Hopefully you can figure out why the original is not functioning.
 
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!!!
 
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 !
 
You must log in or register to reply here.
Back
Top