VBA problem that has me stuck!

[Sebol]

Hello everyone! After how much help these boards have given me in the past, I thought it was time to finally register and say hello!

I'm not sure if I'm going about my macro in the correct fashion but, here is a description of what it's doing.

Sheet 1: I have a cover page with instructions on how to enable the macros to run as error-free as possible (trust documents / network drives) etc. As well as a drop down to select a product to run a macro to try and automate the copy & paste of populating the data in Sheet 2.
Sheet 2: Has a lot of numerical data in it, that each have titles given by Sheet 3, Column A.
Sheet 3: I call this the "Master List", Column A has the titles in it that are branched to sheet 2, and in column B it has the names of the Macros (Similar to Column A, but without spaces and shortened.

I have tried "cell-link" from the drop down to make my life easier but, that didn't get me anywhere.

Private Sub ComboBox1_Change()

Dim Temp As String
Dim MacroName As String
'Dim test As Range
Dim i As Integer

'Set Default Counter
i = 2
'ComboBox selection
Result = ComboBox1.Value

'CellLink (What is currently selected on drop down)
Temp = CStr(Worksheets("List").Cells(2, 4).Value)


'Loop through Macro names, need to find a way to reference to Result/Temp to run macro
MacroName = CStr(Worksheets("List").Cells(i, 2).Value)
    Do While MacroName <> ""
    i = i + 1
    MacroName = Worksheets("List").Cells(i, 2)
    Loop

'Tried making this work with Cell-Link
If Result = Temp Then
    Application.Run "MacroName"
End If

End Sub

Main goal: If Selection = string in column A, then run the macro name in the next column over.

Any help would be appreciated!!

 

[hyat]

Hey Sebol!

Have you tried taking MacroName out of quotes?

Application.Run MacroName

 

[Sebol]

I have, but that isn't my problem, the problem is I am uncertain how to reference the cell next to it. I'm not sure how to set my if statement, If Result = Temp Then Cell A14 = B14, Run "Macro-name-in-B14".

 

[hyat]

Ok so if I understand correctly, you wish to search column A of "List" sheet for Result, and if Result is found, assign the value in the same row of column B to MacroName and execute that macro?

 

[Sebol]

That's correct, currently temp is the variable I made from the Cell-Link of the drop down. But, If we wanted to skip some and just say, If you select this product, grab the MacroName in the next column, and run that macro.

 

[hyat]

We could use Application.WorksheetFunction.Match to match the Result variable with column A. And if it finds a match, it can assign the corresponding value in column B to MacroName and execute the macro.

Private Sub ComboBox1_Change()
Dim Temp As String
Dim MacroName As String
Dim test As Long
Dim Result
 
On Error GoTo ErrorHandler
 
Result = ComboBox1.Value
 
With Worksheets("List")
test = Application.WorksheetFunction.Match(Result, .Range("A:A"), 0)
MacroName = .Range("B" & test).Value
End With
 
Application.run MacroName
 
Exit Sub
 
ErrorHandler:
Debug.Print Err.Description
End
End Sub

 

[Sebol]

That works great!

Now I just have 1 more loop to write to copy and paste templates and I will never have to touch this thing again!, I'll let you know if I get stuck! Thanks again!