Run A Macro Based On Cell Content 1

[diddon]

Hello

I am trying to call a macro from another, but the macro name will variable.

I have a macro that runs down a list of report codes (eg R0001), each of which has a macro of the same name.

I've successfully managed to step down the list and reference the codes in a MsgBox, but cannot get it to run a macro of that same name. The problem seems to lie in this bit of code:

' do stuff to get to cell
rnumber = ActiveCell.Text
Run (rnumber)
' do that report (eg R0001)
' go to top until

Everything else I want to do works fine, but this one bit of code has been getting the better of me for days - so any help would be gratefully received!

cheers
diddon

 

[TonyJollans]

Hi diddon,

It should run exactly as you have it. What error do you get?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Molby]

diddon,

I can't see anything wrong with this code, it works for me as outlined below, (where bob is the activecell text):

Sub test()
a = ActiveCell.Text
Run a
End Sub

Sub bob()
msgbox = "hello"
End Sub

Are you getting an error message? What happens at the line Run (rnumber)?

 

[diddon]

Thanks to both...

I've tried the code above and it works fine (as you knew!), but only when I use the active cell content of bob.

When I try to apply it to a cell in the list I'm using, it breaks down again.

The error message is that it cannot find the macro R0001. (I've double checked the name of the macro and the cell content.)

Could it be the mix of letters and numbers? Should I be "dim rnumber as ..."ing? (that has never been my strong point!)

 

[diddon]

Thanks to both...

I've tried the code above and it works fine (as you knew!), but only when I use the active cell content of bob.

When I try to apply it to a cell in the list I'm using, it breaks down again.

The error message is that it cannot find the macro R0001. (I've double checked the name of the macro and the cell content.)

Could it be the mix of letters and numbers? Should I be "dim rnumber as ..."ing? (that has never been my strong point!)

diddon

 

[Molby]

diddon,

I get the same thing occurring, I can only assume that you cannot use numbers while naming a sub or function (I must admit I've never tried up until now), but I await to stand corrected by one of the other gurus.

 

[Loomah]

diddon
Firstly you should always declare variables!! In this case Dim rnumber as String.

Secondly, is the macro you are trying to run, ie R0001, in a code module or do you have it in object module such as that associated with one of the worksheets?

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Don't ask me why, but when the macro name has a number in it, you have to qualify it with the module name ..

Application.Run "Module1." & rnumber

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[diddon]

That worked treat.

Cheers to everyone for the help. (And I'll try harder in declaring may variable's in future!)

diddon

 

[Loomah]

Just for a Friday afternoon type of thing I was messing around with this and based on Molby's idea I removed the numbers so I was left with
Sub R()
which it still couldn't find

I find this even more bizarre than the original problem as I'm always using sub names of 1 or 2 letters when doing stuff for this forum. The common choices are 'a', 's', 'q', 'aa' all of which I do actually have in an open project at the moment and all of which could be found. But introduce 'R' or 'r' and it was no go!

This leads me to only one conclusion
You can't Run your Rs [off]!

Happy Fiday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Hi Loomah,

It just dawned on me as I read your post. I still don't know why it should be, but you need to qualify anything which could be a column or a cell address (2 letters less than IV with or without a number less than 64K.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

Tony
My poor attempt at humour aside, the point I'm making is that I have subs named 'a', 'aa', 's' & 'q' at the moment and have no problem running them using the Run command from another sub based on the value in a cell. Of all the letters I've tried only 'r' or 'R' fail without qualification.



;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Well, b***** me. I completely give up trying to understand

As for my missing SOH, what can I say? It's been a trying week wouldn't really cut it (even if true). Would you believe lack of caffeine (or other drugs)?

Are you having any joy on the job front - or should we just cry into our beer together?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

Thanks for the offer Tony but I think I'll pass on it!!!

Lack of caffeine should be banned!

As for the job front, it's a long story but I'm basically waiting for a job to come up. I actually need to phone someone today. I'll probably explain the whole thing in TTUK once the dust has settled as the HR issue has been quite farcical - starting with a letter saying my app had been rejected!

Still, it won't stop me crying into my beer when considering it's nearly £3 a pint at the "local"!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

I know I said I give up, and I still don't know WHY but the names which are rejected are those which could be cell references.

"R" does not work
"C" does not work
"R1C1" does not work

and so on and so on and I definitely am not spending any more time on this

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

Tony
Your explanation does me! Have a purple pointy pip

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Most generous of you!!

I would still like to know why - is there some kind of cell content, or something indirectly addressed via a cell, which can be run this way?

Have a good weekend!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.