VBA function returns NUM!

[skutos]

Hi,

I am trying to implement the following function but I just can't get a value. The goal is to sum a row of numbers by matching a date in order to find the correct column and then to sum only the values that match a String in column 4.

Any help would be appreciated


Public Function Calc(Collateral As String, Datum As Integer) As Integer
Dim Column As Integer
Dim rngRequested As Range
Dim rngRequested2 As Range
Dim Test As String
Dim Weight As Integer
Dim Result As Integer
Dim i As Integer
Weight = 0
Set rngRequested2 = Sheets("TotalDomestic").Range("A2:CC650")
Set rngRequested = Sheets("TotalDomestic").Range("A1:CC1")
Column = Application.WorksheetFunction.Match(CLng(Datum), rngRequested, 0)
For i = 1 To 644 Step 1
Test = Application.WorksheetFunction.Index(rngRequested2, i, 4)
If Test = Collateral Then
Result = Application.WorksheetFunction.Index(rngRequested2, i, Column)
Else
Result = 0
End If
Weight = Weight + Result
Next i
Calc = Weight
End Function

Thx

 

[Jon von der Heyden]

=SUMIF(D2:D650,"your string",INDEX(A2:CC650,0,MATCH(date,A1:CC1,0)))

 

[skutos]

Thanks! Do you see by any chance why the VBA code doesn't work? Since I have to use this function in thousands of cells, it would be easiest to just write a VBA function for it

 

[skutos]

Also, how would I implement another criteria into the formula you wrote? I want to say that not only the "String" has to match but also that it should not sum the values that have written "Covered Bond" in Column C.

 

[Jon von der Heyden]

=SUMPRODUCT(--(D2:D650="your string"),--(C2:C650<>"other string"),INDEX(A2:CC650,0,MATCH(date,A1:CC1,0)))

I haven't really spent much effort looking at your UDF because I don't favour it over this native formula method.

 

[skutos]

That function doesn't work unfortunately even tho the parameters look correct to me. I also tried using the * but it still doesn't work (i get a VALUE error)

=SUMPRODUCT(--(TotalDomestic!$D$2:$D$4000=L6);--(TotalDomestic!C2:C4000<>N6);--INDEX(TotalDomestic!$2:$737;0;MATCH($H33;TotalDomestic!$1:$1;0)))

any suggestions?

I appreciate your help

 

[Jon von der Heyden]

Your dimensions are wrong. In your first two arguments you pass rows 2:4000 but in the last you pass 2:737.