MAX/IF in Excel VBA

zdodson

Board Regular
Joined
Feb 29, 2012
Messages
124
Hello all,

I have a vba userform that populates a worksheet given a formula. This formula has two named ranges:

"Ballots" = offset($L$2,,,counta($L:$L)-1)
"DateHelp" = offset($T$2,,,counta($T:$T)-1)

Here is the array formula that I am trying to use:

{=MAX(IF(Ballots=A2,DateHelp))}

I have attempted to populate a column in my worksheet after I hit a command button in my vba userform. Here is the code that I have been working with:

Code:
Private Sub cmdAdd_Click()
Dim WS as Worksheet
Set WS = Sheets("J_ComData")

'Find empty row in the database
 iRow = WS.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row


Range("I2:I" & iRow).FormulaArray = "=MAX(IF(Ballots=A2,DateHelp))"

End sub

The problem that I am having is that the MAX/IF formula will not increment downward (i.e. the formula will not switch from looking at cell A2 to A3, etc.)

Any help on this will be very much appreciated!


Thanks,
Zack
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

zdodson

Board Regular
Joined
Feb 29, 2012
Messages
124
To be even more clear, "DateHelp" represents a range of cells with dates that are in descending order, and "Ballots" is a list of names. The idea is that the vba code will return the latest date of a particular entry, and that each name could have multiple dates associated with it.
 

zdodson

Board Regular
Joined
Feb 29, 2012
Messages
124
This is "solved," for now. I placed the following code in, and it seems to meeting my needs:

Code:
Range("I2").Select
    Selection.FormulaArray = "=MAX(IF(Ballots=RC[-8],DateHelp))"
    Selection.AutoFill Destination:=Range("I2:I" & IRange)
    Range("I2:I" & IRange).Select

The aforementioned code drops the Max/IF function in my cell, and then auto-fills as if you are dragging the formula through your desired range. For this specific scenario, the code drops through a dynamically changing range of cells, but the formula will no populate past my range.
 
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,167,749
Messages
5,855,444
Members
431,733
Latest member
bestassignmentexpert789

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