Results 1 to 7 of 7

Sumproduct UDF function in VBA

Threaded View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Sumproduct UDF function in VBA

    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
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sumproduct and Max function
    By zeez36 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-11-2014, 11:18 AM
  2. [SOLVED] Help with Sumproduct function
    By atandon in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-24-2013, 12:04 AM
  3. [SOLVED] Sumproduct Function with Other Function References
    By T86157 in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 07-30-2012, 04:56 PM
  4. Replies: 9
    Last Post: 07-02-2012, 07:02 PM
  5. Replies: 10
    Last Post: 11-11-2010, 03:49 PM
  6. use of sumproduct function
    By R..VENKATARAMAN in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2006, 11:00 PM
  7. SUMIF Function Inside SUMPRODUCT Function
    By Abdul Waheed in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-19-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1