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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
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 :

Code:
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 Ccol Scol Tcol Ycol Z
Adata1Group 12020/2021
Bdata2Group 12020/2021
Adata3Group 22020/2021
Bdata4Group 22020/2021
Adata5Group 12021/2022
Adata6Group 12021/2022
Adata7Group 12021/2022
Bdata8Group 12021/2022
Bdata9Group 12021/2022
Bdata10Group 12021/2022
Bdata11Group 12021/2022
Edata12Group 12021/2022
Edata13Group 12021/2022
Cdata14Group 22021/2022
Cdata15Group 22021/2022
Cdata16Group 22021/2022

Thanks in advance

Kelly
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
Bump.
I still need help with this.

Can someone please help me out?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
cross posted at:

Reason:
No solution yet
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
solved by @jindon at excelforum.com

Code:
    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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,566
Messages
5,770,893
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top