Excel 2010 VBA - Select Case Failing to Run

[Johnny Thunder]

Hello all,

I have been working on a macro project and have come to a halt.

My macro is looking at a named Range (RunMacroRule) to get a variable 1-3. depending on the variable it will do an action. See code below.

Right now for some reason the code is not running and I am getting the cursor highlighting the "Case Is = 1" saying the "Sub or function is not defined".

Can anyone see an issue with this code or maybe present an alternative. Thanks in advance.

Sub CSV_Transfer_Rules()
   
   Select Case Sheets("License Fee Calculator").Range("RunMacroRule").Value
               
               Case Is = 0
                  Call EnterHeaderLFC
                  Call EnterHeaderODM
                  
                Case Is = 1
                  Call CSV_TransferLFC
                  Call EnterHeaderODM
                  
                 Case Is = 2
                  Call CSV_TransferODM
                  Call EnterHeaderLFC
                                              
                   Case Is = 3
                  Call CSV_Transfer_Combined
                                                              
        End Select


End Sub

 

[HalfAce]

Hi,
I use an alder version of Excel and, while your 'Case Is = 1' syntax works for me, I generally write that statement as: Case 1, Case 2, etc.

And (just to be sure it's not a typo in your post...) It looks to me that your posted code example is actually saying Case l (lower case L) and not Case 1.

Could that be the problem?

 

[Johnny Thunder]

Hi,
I use an alder version of Excel and, while your 'Case Is = 1' syntax works for me, I generally write that statement as: Case 1, Case 2, etc.

And (just to be sure it's not a typo in your post...) It looks to me that your posted code example is actually saying Case l (lower case L) and not Case 1.

Could that be the problem?

In this version of Excel I usually write it as you stated "Case 1" but it auto-converts to this syntax. And there seems to be no typo's in the code, it is a 1 and not an L.

Thank you for looking at it though, I appreciate the time.

 

[RoryA]

You do definitely have routines called CSV_TransferLFC and EnterHeaderODM?

 

[Johnny Thunder]

You do definitely have routines called CSV_TransferLFC and EnterHeaderODM?

Rory, right as you write this I discover I was missing an "_" and the proper macro name was CSV_Transfer_LFC.

But I am still having an issue on the code, now that I corrected the naming, I am still having an issue with only half the macro running.

Specifically my named range is returning a "0" right now so Case 0 should run but for some reason the macro EnterHeaderLFC won't run and only the EnterHeaderODM is running. Now if I put the macro EnterHeaderODM as a Comment to remove it from the code for a test then the Macro EnterHeaderLFC will run fine?

Is there an issue with running 1 macro after the other in a linear fashion? Most of the threads I have seen say that it is allowed.

Sub CSV_Transfer_Rules()
   
   Select Case Sheets("License Fee Calculator").Range("RunMacroRule").Value
               
               Case 0
                 Call EnterHeaderLFC
                 Call EnterHeaderODM
                                 
                Case 1
                  Call CSV_Transfer_LFC
                  Call EnterHeaderODM
                  
                 Case 2
                  Call CSV_Transfer_ODM
                  Call EnterHeaderLFC
                                              
                   Case 3
                  Call CSV_Transfer_Combined
                                                              
        End Select


End Sub

 

[HalfAce]

Hmmm. I'm using XL 2000 and when I pasted your code into the vbe, it did in fact show up as a 1.
I get the same "Sub or function is not defined" error, it's only because I don't have any routines by those names, and the Call EnterHeaderLFC line that is highlighted for me.
I don't know why you're getting the error unless by chance those routines are Private Subs?

 

[Johnny Thunder]

Hmmm. I'm using XL 2000 and when I pasted your code into the vbe, it did in fact show up as a 1.
I get the same "Sub or function is not defined" error, it's only because I don't have any routines by those names, and the Call EnterHeaderLFC line that is highlighted for me.
I don't know why you're getting the error unless by chance those routines are Private Subs?

Not private. Just regular Subs.

This is so odd. I would be more than happy to paste a test doc but there is just way to much data to remove or re-title to post for legal reasons.

"Why can't you just WORK!" LOL

 

[RoryA]

It sounds like one of your routines interferes with the other. Without the code, I can't say more than that.

 

[Johnny Thunder]

It sounds like one of your routines interferes with the other. Without the code, I can't say more than that.

Finally!! I figured it out. I apologize to everyone that got on board to look at this to help but Rory was correct.

The reason I only saw the results of 1 macro was because each time any of the macros run they delete a specified sheets contents so they were both running but when the second macro initiated it deleted the results of the first.

Again, I really appreciate all the help that this forum provides and I will continue to help others out as well.

*Time for me to call it a day before I over look something else! LOL