VBA - "SELECT CASE" question

[manc]

Good day Demigods,

I have the following code sitting in "ThisWorkbook":

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

 

[Norie]

manc

When did column N come into things?

In the original code you were taking a value from column AL and using that in the Select Case.

If it should be column N the value comes from try changing the code I posted to this.

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

 

[manc]

Norie,

Column N is where the button sits that runs the macro.
The button basically is transparent and sits on top of the text that is in cell N6 (if AL3).
I don't mind which is used - the button or a direct reference to cell N6.

I'm not trying to confuse things!

Best regards
manc

 

[Norie]

So the buttons are in column N but the value for the Select Case is in column AL 3 rows below the buttons, is that right?

 

[manc]

Using row 3 as an example...

Value for Select Case is in column AL3
Button sits in N6 (transparent, over text displaying "add/edit")

User clicks on button, select case takes them to correct worksheet based on value in AL3.

Row 3-39 is Job 1
Row 40-76 is Job 2
Row 77-114 is Job 3
etc. etc.
Increments of 37 each time

To continue the example for job 2, value for Select Case would be in AL40
Button sits in N43 (transparent, over text displaying "add/edit")

Sorry for the confusion.
Best regards
manc

 

[Norie]

So the buttons are 3 rows below the values.

Try this.

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

 

[manc]

BOOM!

Norie, code works great. Thanks for your time and patience.
Looking at the code, pretty straight forward to understand how it works, so that it good for my development!
Thanks again.

I do have another question though, and sort of ties in with what you have provided here with another post of mine.
http://www.mrexcel.com/forum/excel-questions/1052011-cascading-drop-down-lists.html
Would a Select Case work with, or be an alternative to, populate a drop-down list?

Basically, what you have provided me works great in helping the user get to the correct worksheet in order to add items to a list for a customer.
However, back to sheet 'H', and below where the button sits, is a drop down list that should display the items associated with Select Case BR.

I won't go on, as i am going off the original topic, but would really appreciate you taking a look and advising if possible.

Thanks for your time
Best regards
manc