Hello everyone
I am trying to return a value based on three criteria ..
Here's my try
Sub GetResult()
Dim Result As Integer
Dim strCourse As String, strVenue As String, strDuration As String
strCourse = Range("G3").Value
strVenue = Range("G4").Value
strDuration = Range("G5").Value
Result = GetData(strCourse, strVenue, strDuration)
Range("G7").Value = Result
End Sub
Public Function GetData(Criteria1 As String, Criteria2 As String, Criteria3 As String) As Variant
Dim Criteria1Column As Range
Dim Criteria2Column As Range
Dim Criteria3Column As Range
Dim ResultColumn As Range
Dim LastRow As Long
With Sheets("Prices")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set Criteria1Column = .Range("A3:A" & LastRow)
Set Criteria2Column = .Range("B3:B" & LastRow)
Set Criteria3Column = .Range("C3:C" & LastRow)
Set ResultColumn = .Range("D3:D" & LastRow)
GetData = Evaluate("SumProduct(" & _
"--(" & Criteria1Column.Address(external:=True) & _
"=""" & Criteria1 & """)," & _
"--(" & Criteria2Column.Address(external:=True) & _
"=""" & Criteria2 & """)," & _
"--(" & Criteria3Column.Address(external:=True) & _
"=""" & Criteria3 & """)," & _
ResultColumn.Address(external:=True) & ")")
End With
End Function
When running the code I got an error "Type mismatch"
Here's the attachment
Thanks advanced for help
Bookmarks