MAX/IF in Excel VBA

[zdodson]

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:

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

 

[zdodson]

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]

This is "solved," for now. I placed the following code in, and it seems to meeting my needs:

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.