Solved: MatchSumOffset

[nsaint]

I wanted to have a function that would look for a particular value in a range of cells and SUM the value in an offset. I used a few examples from other work that has been done and came up with this. I thought I would share it with this group.
Example: is attached.




Function MatchSumOffset(FindVal, rSumRange As Range, Optional ColOffset As Long, Optional RowOffset As Long)
Dim rCell As Range
Dim vResult As Long
Dim lCount As Long, lRow As Long
Dim oVal

For Each rCell In rSumRange
If rCell.Cells.Value = FindVal Then
oVal = rCell.Cells(RowOffset + 1, ColOffset + 1)
vResult = WorksheetFunction.Sum(oVal) + vResult
End If
Next rCell

MatchSumOffset = vResult
End Function

One problem I found and need a little help with is adding values less than 1 without rounding.

So 1.5 and 1.3 should = 2.8 and not 3

[Norie]

Couldn't you just use worksheet functions? eg SUMIF

As to the rounding error, that's because you've declared vResult as a long integer.

Try declaring as Double or Single.

[nsaint]

Of course - thanks.