Excel Trying 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(R[4]C[-33]:R[" & LastRow - 1 & "]C[-33]=""BATCH"",R[4]C[-18]:R[" & LastRow - 1 & "]C[-18])<R[1]C[3]+0.41667-1,R[4]C[-18]:R[" & LastRow - 1 & "]C[-18]))"

 

[muckshifter]

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.

 

[Becky]

Glad to hear you found the solution - thanks for keeping us updated