VBA code to dynamically set a range using multiple criteria (reloaded)

[kelly mort]

So, this was my original question:



I am looking for a way to be able to set MyRng dynamically using other criteria.

I will be looking at column Y and Z.

The data is sorted by col Y then finally by col Z.
ComboBox1 may contain strings like:
Group 1
Group 2
Group 3 and so on.

ComboBox2 contains strings in the form:
Year1/Year2

The criteria are value (string) from ComboBox1 (pointing to col Y) and ComboBox2 (pointing to col Z).

With that being said, the aim is to be able to:

1. set MyRng to start from where the content in ComboBox2 start in the Worksheet (col Z) and end at where the criteria end in same column. That is I want to capture the range that contains the string in Box2 for the column T as shown in the sample code above.

2. Being an upgrade of the request in #1 above, I want to capture (same year as above in Box2) the Group that is found inside ComboBox1. So say I have inside box1 "Group 1" and Box2 "2020/2021", then I want to set the range to start from where the group 1 starts for 2020/2021.



Which produced the result :

Option Explicit

Dim MyRng As Range



Private Sub ComboBox1_Change()

Call Set_Range

End Sub



Private Sub ComboBox2_Change()

Call Set_Range

End Sub



Sub Set_Range()

Dim lr As Long

Dim ini As Long, fin As Long

Dim sh As Worksheet

Dim f As Range



Set sh = Sheet1

lr = sh.Range("T:Z").Find("*", , xlValues, , xlByRows, xlPrevious).Row

If ComboBox2.ListIndex > -1 Then

If ComboBox1.ListIndex > -1 Then

ini = Evaluate("=MIN(IF((Y1:Y" & lr & "=""" & ComboBox1.Value & """)*(Z1:Z" & lr & "=""" & ComboBox2.Value & """),ROW(T1:T" & lr & ")))")

fin = Evaluate("=MAX(IF((Y1:Y" & lr & "=""" & ComboBox1.Value & """)*(Z1:Z" & lr & "=""" & ComboBox2.Value & """),ROW(T1:T" & lr & ")))")

Else

ini = Evaluate("=MIN(IF(Z1:Z" & lr & "=""" & ComboBox2.Value & """,ROW(T1:T" & lr & ")))")

fin = Evaluate("=MAX(IF(Z1:Z" & lr & "=""" & ComboBox2.Value & """,ROW(T1:T" & lr & ")))")

End If

If ini > 0 And fin > 0 Then

Set MyRng = sh.Range("T" & ini & ":T" & fin)

MyRng.Select

End If

End If

End Sub

By @DanteAmor at this link:

(VBA) How to set a variable to dynamically point to a range using multiple criteria



The new request is like this:

I am adding a third combobox which will contain strings like A, B, C, D, E, ….

And these strings; A, B, C, D, E, ……., are contained in column C

So just like before, having say “Group 1” in Box 1, Box 2 having 2020/2021 and box 3 having string E, then I want to set MyRng to capture all E for group 1 and 2021/2022

Data layout looks like this:

col C	col S	col T	col Y	col Z
A		data1	Group 1	2020/2021
B		data2	Group 1	2020/2021
A		data3	Group 2	2020/2021
B		data4	Group 2	2020/2021
A		data5	Group 1	2021/2022
A		data6	Group 1	2021/2022
A		data7	Group 1	2021/2022
B		data8	Group 1	2021/2022
B		data9	Group 1	2021/2022
B		data10	Group 1	2021/2022
B		data11	Group 1	2021/2022
E		data12	Group 1	2021/2022
E		data13	Group 1	2021/2022
C		data14	Group 2	2021/2022
C		data15	Group 2	2021/2022
C		data16	Group 2	2021/2022

Thanks in advance

Kelly

 

[kelly mort]

Bump.
I still need help with this.

Can someone please help me out?

 

[kelly mort]

cross posted at:

vba code to dynamically set a range using multiple criteria (reloaded) [SOLVED]

Cross posted at: https://www.mrexcel.com/board/threads/vba-code-to-dynamically-set-a-range-using-multiple-criteria-reloaded.1177373/ reason: No solution (s) yet. So, this was my original question: I am looking for a way to be able to set MyRng dynamically using other criteria.

www.excelforum.com

Reason:
No solution yet

 

[kelly mort]

solved by @jindon at excelforum.com

vba code to dynamically set a range using multiple criteria (reloaded) [SOLVED]

Cross posted at: https://www.mrexcel.com/board/threads/vba-code-to-dynamically-set-a-range-using-multiple-criteria-reloaded.1177373/ reason: No solution (s) yet. So, this was my original question: I am looking for a way to be able to set MyRng dynamically using other criteria.

www.excelforum.com

    Dim cb1, cb2, cb3, myMin, myMax, rng As Range, s As String
    cb1 = ComboBox1.Value: cb2 = ComboBox2.Value: cb3 = ComboBox3.Value
    If cb2 = "" Then Exit Sub
    With Sheets("sheet1")
        With .Range("z1", .Range("z" & Rows.Count).End(xlUp))
            s = "(" & .Address & "=""" & cb2 & """)" & IIf(cb1 <> "", "*(" & .Offset(, -1).Address & "=""" & cb1 & """)", "") & _
                IIf(cb3 <> "", "*(" & .Offset(, -23).Address & "=""" & cb3 & """)", "")
            myMin = .Parent.Evaluate("min(if(" & s & ",row(" & .Address & ")))")
            If myMin = 0 Then MsgBox "No matched data", vbCritical: Exit Sub
            myMax = .Parent.Evaluate("max(if(" & s & ",row(" & .Address & ")))")
        End With
        Set rng = Intersect(.Rows(myMin & ":" & myMax), .Columns("t"))
    End With
    Application.Goto rng