Relative Locations

I'm making a price-estimating sheet for our office. So far I've got everything working the way I want. The sheet takes up cells A1 through O23. Various drop-down lists within the estimator trigger certain macros to run, changing other areas of the sheet.

My question is what is the best way to reference certain cells in the sheet without referring directly to their address? The reason for this is that I am planning on duplicating the exact same section multiple times on one sheet, with each one acting independently from the other.

So for instance, pushing the reset button should only effect the region from A1 to O23. If I have a duplicate of A1:O23 below in the area A24:O66, and I push its reset button, again, it should only effect A24:066. The same goes for selecting other portions of the list.

I can make this work by writing massive code and very repetitive code specifying certain ranges and such, but for cleanliness, I want code that can work for as many duplicates as I want.

Hopefully I've been somewhat clear on this. I've posted a couple of screen shots aswell,

jamgood96,

Try this bit of code for each button:

Sub btnReset_Click()

    Dim rngArea As Range:       Set rngArea = ActiveSheet.Range("A1:O23")
    Dim FindLocation As Long:   FindLocation = 0
    Dim btnReset As Button:     Set btnReset = ActiveSheet.Buttons(Application.Caller)
    While Intersect(btnReset.TopLeftCell, rngArea.Offset(FindLocation, 0)) Is Nothing
        FindLocation = FindLocation + 23
    Wend
    
    Set rngArea = rngArea.Offset(FindLocation, 0)
    MsgBox rngArea.Address
    
    'Continue your resetcode.  Replace manual ranges with "rngArea" (no quotes)
    
End Sub

Hope this helps,
~tigeravatar

Great, I'll play around with that some. I did notice that it is still reliant upon calling the range A1:023. So if I have a similar setup below that range, I'll have to have a separate piece of code for that range, correct?

jamgood96,

You would only need to specifically designate different ranges if the area below the first one (A1:023) is of a different size. If its the same size (uses columns A to O and rows 1 to 23) then the macro will find the appropriate range for you.

What the macro does is get the address of the TopLeftCell that the button clicked is touching. It then goes through each area of size identical to A1:O23 (checks that first, then checks A24:O46, etc) and sets the area to the one that contains the address of that TopLeftCell.

Hope that helps,
~tigeravatar

Originally Posted by tigeravatar

jamgood96,

You would only need to specifically designate different ranges if the area below the first one (A1:023) is of a different size. If its the same size (uses columns A to O and rows 1 to 23) then the macro will find the appropriate range for you.

What the macro does is get the address of the TopLeftCell that the button clicked is touching. It then goes through each area of size identical to A1:O23 (checks that first, then checks A24:O46, etc) and sets the area to the one that contains the address of that TopLeftCell.

Hope that helps,
~tigeravatar

Ok, great. I think that makes sense.

Along with the reset button, I have other functions reliant upon certain areas of the range. For instance, I need a macro to execute if any cells within range B3:B13 change. Of course, I would like to reuse this same macro in the duplicate below for ranges B36:B46.