VBA loop that runs a different macro based on cell value

[Nickw2619]

Hi,

Ive been having a lot of trouble googling this, so thanks in advance for any help you can give. Basically I have a table of data with a number(1-10) in the column at the end (G) I have 10 macros that I want to run based on the number in G. So if the numbers 2 run Macro2 if it's 7 run Macro7. At the end of all the macros the top row gets deleted so what I would like to do is make a until A2 is empty loop that runs the macro corresponding to the value in G2. If anyone can help I'd greatly appreiate it and if I need to explain more let me know.

Thanks!

 

[frank_AL]

Nick,

Give this code a try:

Option Explicit
Sub InitiateMacros()
Dim currval As Integer
Dim i As Long


Do Until Cells(2, 1) = ""
    currval = Cells(1, 7).Value
    Select Case currval
        Case 1
            Call Macro1
        Case 2
            Call Macro2
        Case 3
            Call Macro3
        Case 4
            Call Macro4
        Case 5
            Call Macro5
        Case 6
            Call Macro6
        Case 7
            Call Macro7
        Case 8
            Call Macro8
        Case 9
            Call Macro9
        Case 10
            Call Macro10
    End Select
    i = i + 1
Loop


End Sub

 

[Nickw2619]

Frank,

Did it without the loop just to test it and make sure it works and it was working flawlessly. I'm curious what the Option Explicit before the Sub means and what it's for.

Thanks

 

[frank_AL]

Option Explicit means that you must declare all/any variables you use in your code module. If you monitor this Forum you will see that reiterated by all of the experts that have posted thousands of times here. It insures you know what variables you are using and what type of variable it is set to be. Always better to declare a variable as a specific type as opposed to Variant which uses more memory.