Find columns where week row and year row matches values given in another cell range-CodePudding

I have a matrix in excel where the 2 top rows are "Year" and "Week".

As you probably could imagine there is a lot of columns in this matrix as time goes by. Therefore I would like a button that simply sends the user to the column with year and week matching the 2 "search cells"

To illustrate:

What I want is just to select the column or Year-Week range that matches the criterias in cell B1 and B2.

So that it would select column C or range("C4:C5") with the criterias given in the picture.

I simply cant wrap my head around it atm, so I hope you can help me.

Thanks in advance!

CodePudding user response：

here example:

Sub test()

Dim criteria1$, criteria2$
Dim cl As Range, data As Range

criteria1 = [B1].Value
criteria2 = [B2].Value

Set data = [B4:G5]

For Each cl In data
    If cl.Value = criteria1 And cl.Offset(1, 0).Value = criteria2 Then
        Range(cl, cl.Offset(1, 0)).Select
    End If
Next cl

End Sub

you can assign this Sub to button/shape/picture ...

CodePudding user response：

You can use COUNTIF first to check if the value exist and then EVALUATE to get column number and select

Sub test()
Dim ThisColumn As Integer

'first, check that year and week exists
If Application.WorksheetFunction.CountIfs(Range("A4:G4"), Range("B1"), Range("A5:G5"), Range("B2")) = 0 Then
    'not found
    MsgBox "Year and week not found", vbCritical, "ERROR"
Else
    'found, then select
    ThisColumn = Evaluate("=MATCH(B1&B2,TRANSPOSE(A4:G4)&TRANSPOSE(A5:G5),0)")
    Range(Cells(4, ThisColumn), Cells(5, ThisColumn)).Select
End If

End Sub

If you put a year and or a week not found, then it will raise an error:

Please, note that you'll need to adapt the ranges in case you have more values at rows 4 and 5