nsaint
12-29-2007, 10:27 AM
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
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