Help with MAXIFS and MINIFS

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
The following formula seems to work. =MINIFS(F4:X4,$G$7:$Y$7,"COMPLIANT"). In the range F4:X4, I want to find the MIN of F4,H4,J4,L4,N4,P4,R4,T4,V4,X4 if the condition applies. (I'll want to do the MAXIFS as well)
I use the following VBA to add rows to the table
VBA Code:
Do Until z = 0
Set tblRow = tbl.ListRows.Add
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop
The code works well except that the MINIFS formulas get changed to F4:X9, F5:X10, F6:X11, etc. instead of F4:X4, F5:X5, F6:X6, etc.

Need help to accomplish this, not necessarily with using MINIFS or MAXIFS. Thanks
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Got it working using this. Runs perfectly from the VBA module but I get an error when I use the button in sheet Calc
VBA Code:
With tbl.Range
lr = .Rows(.Rows.Count).Row + 3
Range("D4:D7").FormulaR1C1 = "=MINIFS(RC6:RC24,R" & lr & "C7:R" & lr & "C25,""COMPLIANT"")"
Range("E4:E7").FormulaR1C1 = "=MAXIFS(RC6:RC24,R" & lr & "C7:R" & lr & "C25,""COMPLIANT"")"
End With
 
Solution

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
Been offline for a while, glad to see you figured it out :)

You just need to add the sheet name in front of the ranges for it to work on the button, without a sheet name, Range is always applied to the selected sheet.
As it's a table, you should be able to refer to it directly, I'm not entirely sure of the correct syntax for it but will take a look later for you.
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Yes, I figured that was the issue. Thanks very much for your help. I am very new to VBA and this was very helpful. Take care!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,945
Messages
5,621,760
Members
415,854
Latest member
Tutu123

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