Select range based on column & row values

[phad012]

Hello,

I have table like the one below. I'd like a macro to select a range in that table which I specify.

For example:

• if I type 'Budget' in cell B2 and 'Quarter 4' in cell B3, I'd like the macro to select range F11:F14; or

• if I type 'Actual' in B2 and 'Quarter 2' in B3, I'd like the macro to select range D6:D10

[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Row input:[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Column input:[/TD]
[TD]Quarter 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Line[/TD]
[TD]Type[/TD]
[TD]Quarter 1[/TD]
[TD]Quarter 2[/TD]
[TD]Quarter 3[/TD]
[TD]Quarter 4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Revenue[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Personnel costs[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Admin costs[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]R&D costs[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Profit[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Revenue[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Personnel costs[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Admin costs[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]R&D costs[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense. Any guidance appreciated

 

[BrianJN1]

This short macro works for this table.
Obviously you are going to have to adjust some values as you lengthen the ranges.

Sub GetRange()
Dim rw As Long
Dim cl As String
If Range("B2").Value = "Budget" Then
rw = 11
Else
rw = 6
End If
cl = Chr(66 + Right(Range("B3").Value, 1))
Range(cl & rw & ":" & cl & rw + 4).Select
End Sub

In case you are wondering, cl starts with CHR(66) which refers to "B". It is increased by the value (1 to 4) of the Quarter that you enter.

 

[phad012]

This short macro works for this table.
Obviously you are going to have to adjust some values as you lengthen the ranges.

Sub GetRange()
Dim rw As Long
Dim cl As String
If Range("B2").Value = "Budget" Then
rw = 11
Else
rw = 6
End If
cl = Chr(66 + Right(Range("B3").Value, 1))
Range(cl & rw & ":" & cl & rw + 4).Select
End Sub

In case you are wondering, cl starts with CHR(66) which refers to "B". It is increased by the value (1 to 4) of the Quarter that you enter.

Thanks Brian, much appreciated.

I probably should have mentioned my data set is a bit longer. What I I copied above was a simplification.

The real data set is around:

• 100 columns - the quarters are for a number of years E.g. Q1 2007, Q2 2014, etc (not just Q1-Q4)
• 5000 rows - there are around 20 row categories. Not just 'Actual' and 'Budget', but also things like Revised Budgets, Draft Actuals, Plans set at varying dates

So it'd be great to get something which is suited to a larger data set.

Many thanks

 

[MARK858]

Possibly....

Sub SelectRng()
    Dim x As Long, y As Long, z As Long

    x = Range(Cells(5, 2), Cells(5, Cells(5, Columns.Count).End(xlToLeft).Column)).Find(What:=Range("B3").Value, After:=Range("B5"), LookIn:=xlValues, _
                            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    With Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row)

        y = .Find(What:=Range("B2").Value, After:=Range("B5"), LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

        z = .Find(What:=Range("B2").Value, After:=Range("B5"), LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    End With

    Range(Cells(y, x), Cells(z, x)).Select

End Sub

 

[phad012]

Works perfect thanks so much!

That's my first post on this forum and I'm hugely impressed with the quick responses to a problem which I thought would take ages to solve.

Saved me a lot of time and I'm very grateful, thank you

Possibly....

Sub SelectRng()
    Dim x As Long, y As Long, z As Long

    x = Range(Cells(5, 2), Cells(5, Cells(5, Columns.Count).End(xlToLeft).Column)).Find(What:=Range("B3").Value, After:=Range("B5"), LookIn:=xlValues, _
                            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    With Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row)

        y = .Find(What:=Range("B2").Value, After:=Range("B5"), LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

        z = .Find(What:=Range("B2").Value, After:=Range("B5"), LookIn:=xlValues, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    End With

    Range(Cells(y, x), Cells(z, x)).Select

End Sub

 

[MARK858]

Welcome to the forum and happy it helped.

The code could be shortened a bit to make it tidier but one thing you should really do is change the 3 places xlPart appears to xlWhole as it is more appropriate for what you are doing (I was a bit lazy and recorded some of the code to start with ).