VBA - "SELECT CASE" question

M

manc

Active Member
Joined
Feb 26, 2010
Messages
340
Good day Demigods,

I have the following code sitting in "ThisWorkbook":
Code: 
Sub Button_Goto_Correct_Consignee()

    Dim BR As Integer


    BR = Range("'H'!AL3")


    Select Case BR
    
        Case Is = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD1").Range("A1"))
        Case Is = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_FH2").Range("A1"))
        Case Is = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IF3").Range("A1"))
        Case Is = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF4").Range("A1"))
        Case Is = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ5").Range("A1"))
        Case Is = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD6").Range("A1"))
        Case Is = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SM7").Range("A1"))
        Case Is = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SA8").Range("A1"))
        Case Is = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WS9").Range("A1"))
          
    End Select
    
End Sub

Works great because i am only working on 'H'!AL3 at the moment, whilst i design the worksheet.
However, the range is going to change each time i copy the rows down.
I know what the range is going to be each time - the next one is 'H'!AL40, followed by 'H'!AL77 - a difference of plus 37 rows each time.

How can i adapt the above code to take this into consideration, to keep going an infinite number of times?

Any help/suggestions greatly appreciated, as always.

Enjoy your day
Best regards
manc
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
This would loop until the AL cell value doesn't equal 1-9.

Code: 
[COLOR=darkblue]Sub[/COLOR] Button_Goto_Correct_Consignee()
    
    [COLOR=darkblue]Dim[/COLOR] BR [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR], [B]r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/B]
    
    [B][COLOR=darkblue]For[/COLOR] r = 3 [COLOR=darkblue]To[/COLOR] Rows.Count [COLOR=darkblue]Step[/COLOR] 37[/B]
    
        BR = Range("'H'!AL" [B]& r[/B])
        
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] BR
        
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD1").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_FH2").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IF3").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF4").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ5").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD6").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SM7").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SA8").Range("A1"))
            [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WS9").Range("A1"))
[B]            [COLOR=darkblue]Case[/COLOR] Else: [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR][/B]
            
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
[B]    [COLOR=darkblue]Next[/COLOR] r[/B]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
Or do you just want to find the last used cell in column AL and use its 1-9 value?

Code: 
[color=darkblue]Sub[/color] Button_Goto_Correct_Consignee()
    
    [color=darkblue]Dim[/color] BR [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    
[B]    BR = Range("'H'!AL" & Rows.Count).End(xlUp).Value [COLOR=#008000]'Last used cell in column AL[/COLOR][/B]
    
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] BR
    
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD1").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_FH2").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IF3").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF4").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ5").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD6").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SM7").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SA8").Range("A1"))
        [color=darkblue]Case[/color] [color=darkblue]Is[/color] = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WS9").Range("A1"))
        
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Hi AlphaFrog, thanks for your posts.

Apologies, I maybe didn't explain very well.

Each row in my worksheet is a "job".
So row 3 in this example contains many many columns. In cell AK3 is a drop-down list containing customers, and depending on what customer is chosen, AL3 looks up its unique identifier.
Customer 1's unique identifier is 1, Customer 2 is 2, 3 is 3 etc.
If a user selects Customer 1 from the drop-down list, the above VBA code restricts what sheets can be accessed using the value in AL3. So in theory, i may have 4 or 5 Select Cases in VBA, so that the user only see's worksheets applicable to Customer 1.

Row 4 contains the next "job"
Row 5 contains the next "job"
etc.

I am looking for the above VBA code to work regardless of what row the user is on. So, if they are on row 25 for example, the code will be
Code: 
BR = Range("'H'!AL25")
I could have 10,000 rows, so the range needs to be specific to the row.

Hope i have explained my problem better.
Best regards
manc
 
Upvote 0
Sub Button_Goto_Correct_Consignee()
Dim BR As Integer

BR = Range("'H'!AL" & Rows.Count).End(xlUp).Value 'Last used cell in column AL

Select Case BR

Case Is = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD1").Range("A1"))
Case Is = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_FH2").Range("A1"))
Case Is = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IF3").Range("A1"))
Case Is = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF4").Range("A1"))
Case Is = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ5").Range("A1"))
Case Is = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD6").Range("A1"))
Case Is = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SM7").Range("A1"))
Case Is = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SA8").Range("A1"))
Case Is = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WS9").Range("A1"))

End Select
End Sub
Click to expand...

Thanks for this AlphaFrog, but i'm getting an error "type mismatch"

Any ideas?
Best regards
manc
 
Last edited:
Upvote 0
manc said:
Nothing happens when code is run.

Best regards
manc
Click to expand...

Dear AlphaFrog,

Apologies, I see what you have done.
The code DOES work if the user selects a cell on the active row and clicks the button.
However, the button is not on the active row, and chances are the user will not be on the active row either, as rows 4-36 are grouped to row 3, and contain "job" information. So the user could be anywhere between B3:DV36 (it's not that big, column width is only 15!).

AL3 is the only constant (button user clicks is approx. N6:Q6)
Then for the next job it will be AL40 (button user clicks is approx. N43:Q43).
Then for the next job it will be AL77 (button user clicks is approx. N80:Q80).
And so on and so on (steps of 37 each time)

Hope this helps
Best regards
manc
 
Upvote 0
manc

How are you executing the code?

Is the same macro assigned to all the buttons?

Are the buttons always 3 rows below down from the row you want in AL?

If the answer to those 2 questions is yes then we might be able to use Application.Caller, something like this.
Code: 
Sub Button_Goto_Correct_Consignee() 
Dim BR As Long

    BR = Sheets("H").Range("AL" & Sheets("H").Shapes(Application.Caller).TopLeftCell.Row+3).Value

    Select Case BR

        Case Is = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD1").Range("A1"))
        Case Is = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_FH2").Range("A1"))
        Case Is = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IF3").Range("A1"))
        Case Is = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF4").Range("A1"))
        Case Is = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ5").Range("A1"))
        Case Is = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD6").Range("A1"))
        Case Is = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SM7").Range("A1"))
        Case Is = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SA8").Range("A1"))
        Case Is = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WS9").Range("A1"))

    End Select

End Sub
 
Upvote 0
Dear Norie,

I've tried your code but nothing happens.

The macro is being executed when a button is pressed.
The same macro is attached to all the buttons.
The button is always 3 rows below AL.

If it is easier, then instead of a button, a cell can be referenced.
For example if AL3 is the cell in question, then when user clicks a hyperlink or text reference in cell N6, the code is run.
AL3 = N6
AL40 = N43
AL77 = N80
etc.

Best regards
manc
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA help with Select Case or Case
Replies
1
Views
292
My Aswer Is This
My Aswer Is This
J
  • Question Question
VBA to Create Table and to Remove Duplicates
Replies
0
Views
1K
jessebh2003
J
M
  • Question Question
VBA - change SELECT CASE code associated with a CommandButton to a Hyperlink
Replies
6
Views
961
My Aswer Is This
My Aswer Is This
M
  • Solved
VBA Code to split week ranges into separate lines.
2
Replies
10
Views
2K
Joe4
Joe4
R
  • Question Question
Vba Runs slow
Replies
2
Views
300
Rampestamper
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top