Help with MAXIFS and MINIFS

[JARichard74]

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

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]

Got it working using this. Runs perfectly from the VBA module but I get an error when I use the button in sheet Calc

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

 

[jasonb75]

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]

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!