VBA; Select min or max in range depends on argument

[KVikLine]

Hi,
I'm truing to get min and max of range depends on selected argument via VBA. Unfortunately, without success.
An example of data source is attached.
E.g. I will found out (via VBA) which min and max "wi" contains to "th" = 7. Result should be: min =180 and max = 570.
Thank you for help.

th	wi
10	100
10	250
10	400
10	550
10	700
10	850
9	200
9	330
9	460
9	590
9	720
9	850
9	980
7	180
7	310
7	440
7	570
6	203
6	333
6	463
6	593
6	723
6	853

 

[Joe4]

Welcome to the Board!

Why do you need VBA? This can be done pretty easily with the MINIFS and MAXIFS functions.
See: MINIFS Function

 

[KVikLine]

Hi Joe4, I'm working on Add-In. This operation is a part of it.

 

[Joe4]

You can call/use Excel functions in VBA like this:

Application.WorksheetFunction.MinIfs(...)

 

[KVikLine]

Cool, thank you, it works....
Below extract based/from example:

th	wi		th_sel
10	100		6
10	250
10	400		Answer_vba		Answer (formula)
10	550		853	max	853
10	700		203	min	203
10	850
9	200
9	330
9	460
9	590
9	720
9	850
9	980
7	180
7	310
7	440
7	570
6	203
6	333
6	463
6	593
6	723
6	853

Formula:

Test_group.xlsm
	F
4	Answer (formula)
5	853
6	203
Sheet1

Cell Formulas
Range	Formula
F5	F5	=MAXIFS($B$2:$B$24,$A$2:$A$24,$D$2)
F6	F6	=MINIFS($B$2:$B$24,$A$2:$A$24,$D$2)

AND code in VBA (finally, will be adjusted to/in Add-In)

Sub test_Th()
WBS_Name = ActiveWorkbook.Name
WSS_Name = ActiveSheet.Name
'choose th
th_sel = Workbooks(WBS_Name).Worksheets(WSS_Name).Cells(2, 4)
'verify min max based on selected th
Wi_max = Application.WorksheetFunction.MaxIfs(Workbooks(WBS_Name).Worksheets(WSS_Name).Range("B2:B24"), Workbooks(WBS_Name).Worksheets(WSS_Name).Range("A2:A24"), th_sel)
Wi_min = Application.WorksheetFunction.MinIfs(Workbooks(WBS_Name).Worksheets(WSS_Name).Range("B2:B24"), Workbooks(WBS_Name).Worksheets(WSS_Name).Range("A2:A24"), th_sel)
'write Answer
Workbooks(WBS_Name).Worksheets(WSS_Name).Cells(5, 4) = Wi_max
Workbooks(WBS_Name).Worksheets(WSS_Name).Cells(6, 4) = Wi_min
End Sub

 

[Joe4]

Excellent!

Glad I was able to help and it all worked out for you!

Note that if you want to shorten your formulas a little bit, you could set your ranges to range objects, and just use those in your formula (since you are using the same ranges for each formula).
Not necessary, but sometimes makes things look a little "cleaner".

 

[KVikLine]

You mean like this?
Below an extract from Add-In:

.........
Dim Wi_Range As Range
Dim Th_Range As Range
Set Wi_Range = Range(Workbooks(WB_Name).Sheets("Tabl_DimTab").Cells(2, 2), Workbooks(WB_Name).Sheets("Tabl_DimTab").Cells(Tabl_DimTab_LastRow, 2)) 
Set Th_Range = Range(Workbooks(WB_Name).Sheets("Tabl_DimTab").Cells(2, 1), Workbooks(WB_Name).Sheets("Tabl_DimTab").Cells(Tabl_DimTab_LastRow, 1)) 
'short and easy part like in row below?
Wi_Max = WorksheetFunction.Application.MaxIfs(Wi_Range, Th_Range, Th_to_use)
.........