Need help with cell-value-dependent macros

[Nivram3330]

Hi folks. I love Excel but I know just enough to be dangerous, or to get myself stuck, and I know basically nothing about VB. I have a single worksheet on which I have 9 macros that I recorded. I also have a command button which, when clicked, is supposed to run one of the macros. It is this that complicates things beyond my ability. Whether each macro runs is supposed to be dependent on there being a Y in the cell I want the macro to evaluate. My macro names and the cells which need to be evaluated are as follows:

Macro Name Cell to Evaluate
Jan22................AH29
Mar5.................AI29
Apr16................AJ29
May28...............AK29
Jul9..................AL29
Aug20...............AM29
Oct1.................AH30
Nov12...............AI30
Dec24...............AJ30

Only one of the above cells will ever have a Y in it at any one time. I would like the script to have the macro run through the cells and, when it finds the one with the Y, run that macro, but obviously skip the others. I tried the following to see if I was on the right track, but this runs the macro regardless of the contents of cell AH29. I'm sure it's ridiculously wrong but, as I said, I know nothing about VB.


Private Sub CommandButton1_Click()

If AH29 = Y Then
Application.Run "Jan22"
End If

End Sub


I know I'm a noob, but I sure would appreciate any help.

Thanx Everyone

Nivram3330

 

[shg]

Welcome to the board.

If Range("AH29").Value = "Y" Then

 

[Tetra201]

Try something like this (note: not tested):

Private Sub CommandButton1_Click()
    cAddr = Array("AH29", "AI29", "AJ29", "AK29", "AL29", "AM29", "AH30", "AI30", "AJ30")
    mName = Array("Jan22", "Mar5", "Apr16", "May28", "Jul9", "Aug20", "Oct1", "Nov12", "Dec24")
    For i = LBound(cAddr) To UBound(cAddr) Step 1
        If Range(cAddr(i)).Value = "Y" Then Application.Run mName(i): Exit Sub
    Next i
End Sub

 

[Nivram3330]

Tetra201,

Thank you so much!! This worked perfectly the very first time. Sure is nice to have help from a community with such considerate members.

 

[Tetra201]

Glad to have been of help.