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

[kelly mort]

Sub test  ()
   Dim lr As Long 
   Dim MyRng As Range 
   Dim sh As Worksheet 

   Set sh = Sheet1
   lr = sh.Cells(Rows.Count, 1).End (xlUp).Row 

    Set MyRng = sh.Range ("T4:T" & lr)

End Sub

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.

Thanks in advance.

 

[DanteAmor]

Trying to imagine how you have your data in columns Y and Z, the following image comes to mind.

If the above example is correct, then if you select Group1 and 2021/2022 the range in T is T6:T14.
If the above is also correct, then try the following:

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

 

[kelly mort]

@DanteAmor ,

That's an amazing job! Thanks.

In case I want to use hard-coded values for the ComboBox1.

In this case, I have 5 variables; MyRng1 to MyRng5.

I want to set the MyRng1 to all those having Group 1, ......., MyRng5 to all those having Group 5 (same string in box2).

How do I do that (the best way without duplicating most of your lines)?

 

[DanteAmor]

That's an amazing job! Thanks.

I'm glad to help you. Thanks for the feedback.

How do I do that (the best way without duplicating most of your lines)?

You can put an example of how your data is on the sheet and explain what you need.

 

[DanteAmor]

How about

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
  Dim MyRng1 As Range, MyRng2 As Range, MyRng3 As Range, MyRng4 As Range, MyRng5 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 & ")))")
      Select Case ComboBox1
        Case "Group 1": Set MyRng1 = sh.Range("T" & ini & ":T" & fin)
        Case "Group 2": Set MyRng2 = sh.Range("T" & ini & ":T" & fin)
        Case "Group 3": Set MyRng3 = sh.Range("T" & ini & ":T" & fin)
        Case "Group 4": Set MyRng4 = sh.Range("T" & ini & ":T" & fin)
        Case "Group 5": Set MyRng5 = sh.Range("T" & ini & ":T" & fin)
      End Select
    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 & ")))")
      Set MyRng = sh.Range("T" & ini & ":T" & fin)
      MyRng.Select
    End If
  End If
End Sub

 

[kelly mort]

@DanteAmor

Sorry I could not reply earlier and couldn't post the demo workbook as well (I am having challenges accessing my Dropbox account).

The latest code you posted is very close to what I want to achieve.

We are eliminating the ComboBox1 altogether.

Instead, we use those "Group 1" to "Group 5" as hard-coded criteria.

So instead of using the case statement, which always return just one output at a time, I want to get all the 5 outputs at the same time (if only there are data for them in the database)

So the code will check Group 1 against the string in ComboBox2 if found, it sets that range as MyRng1

Then we move to Group 2 and do same, setting range to MyRng2 and so on..

 

[DanteAmor]

You agree if we use the first version of your original request and you create a new thread for this new request.