Make a Date variable equal a single-cell array formula

Hi,

I have a working single-cell array formula in cell G18: {=MAX(IF(F21=DOA_ApprovalType,DOA_EffectiveDate))}

Capture.PNG

I am creating a sub to read in an ApprovalType (replacing F21 above). I want to use the above formula in vba to get the single date result which i want to store in a date variable to pass on to a user defined function. I can't get the above function to calc or store except to write the formula on an sheet which then calculates.
Any ideas to get it to work without going to the sheet will be much appreciated.

I have being trying for hour but don't know enough to get it to work.

Regards,
Shamala

Use Application.Evaluate.

Thanks that seems to work but i must be applying it incorrectly because i get a Type mismatch error on the same line:

Sub FindDOA()

Dim r As Integer
Dim s As String
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
Dim ApprovalType As String
Dim ETC As Integer
Dim TempDate As Date

r = ActiveCell.Row
s = Range("a" & r).Value

' Promt
strPrompt = "Are you sure you want to re/calculate the DOA for file " & s & "?"

' Dialog's Title
strTitle = "Be carefull not to override DOA requirements, especially when the" & vbCr & "DOA Policy has changed."

'Display MessageBox
iRet = MsgBox(strPrompt, vbOKCancel, strTitle)

' Check pressed button
If iRet = vbCancel Then
MsgBox "Cancelled."
Else
'get ApprovalType and ETC
ApprovalType = Range("c" & r).Value
ETC = Range("h" & r).Value

TempDate = Application.Evaluate("={MAX(IF(ApprovalType=DOA_ApprovalType,DOA_EffectiveDate))}")

MsgBox "Yes!"
End If



End Sub

Remove the curly brackets, these are added by Excel on the spreadsheet.

Try this

Please Login or Register  to view this content.