# ExcelTrying to code an Array Function in VBA Excel to works as a MaxIF

#### BlueBoy

Hello!

I am new to VBA and still have to learn some of the basics, but I am trying to learn as I go.

I am trying to code an Array Function in Excel VBA to take a range of dates and get the maximum value (date) that is less than a given value (the constraint, which would also be a date).
I will be using this function in a macro, though I can't seem to get it to work. The code currently will run, but the value given is a #VALUE! Error that tells me that "A value used in the formula is the wrong type".

Here is what I currently have: Here is the text version so you can copy/paste

Function MaxIf_Array(List, Constraint) As Double

'Function takes a list and returns the Max value that is below a given constraint

Dim Const_Array As Variant
Dim Cell As Range
Dim SmallerThan As Long

'Transfer data to Const_Array while filtering for data smaller than Constraint

For Each Cell In List
If Not Cell > Constraint Then
SmallerThan = SmallerThan + 1
ReDim Preserve Const_Array(1 To SmallerThan)
Const_Array(SmallerThan) = Cell.Value
End If
Next Cell

'Use the Worksheet Max function on values in Array

MaxIfArray = WorksheetFunction.Max(Const_Array)

End Function

Any help would be appreciated!

Thanks!

#### BlueBoy

Never mind. I was able to figure it out by using a Function Array. Thanks anyway!

Dim LastRow As Long
LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Range("AP1").Select
ActiveCell.FormulaArray = _
"=MAX(IF(IF(RC[-33]:R[" & LastRow - 1 & "]C[-33]=""BATCH"",RC[-18]:R[" & LastRow - 1 & "]C[-18])<RC+0.41667-1,RC[-18]:R[" & LastRow - 1 & "]C[-18]))"  • Ian and bootneck02

#### muckshifter

##### I'm not weird, I'm a limited edition.
Moderator
Never mind. I was able to figure it out by using a Function Array.
thanks for letting us know ... I know nuffin about Excel stuff. Glad to hear you found the solution - thanks for keeping us updated 