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


Joined
Jul 15, 2016
Messages
2
Reaction score
2
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:
upload_2016-7-15_16-32-1.png


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!
 
Ad

Advertisements

Joined
Jul 15, 2016
Messages
2
Reaction score
2
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]))"

upload_2016-7-19_9-18-21.png

upload_2016-7-19_9-19-5.png
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
Glad to hear you found the solution - thanks for keeping us updated :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top