PDA

View Full Version : Solved: Search for cell values then sum scell to......



fizcalpolicy
06-05-2010, 08:01 AM
VBA newbie here

I want to search for specifc cell values, then sum the values to the left of each value found. Example - "excess" finds N9, H15 , J22; now I want the sum of all (left) cell values : M9, G15, I22, etc

Thanks

Fiz

mdmackillop
06-05-2010, 08:31 AM
Welcome to VBAX

Option Explicit
Sub FindExcess()
Dim Total As Long
Dim c As Range
Dim FirstAddress As String
Dim ToFind As String

ToFind = InputBox("Text to find", "Custom Search", "excess")
With ActiveSheet.Cells
Set c = .Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Total = Total + c.Offset(, -1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
MsgBox Total
End Sub

Bob Phillips
06-05-2010, 09:10 AM
You could try an array formula, such as

=SUM(IF(B1:N19="abc",A1:M19))

fizcalpolicy
06-06-2010, 05:30 AM
Thanks-a-million!!

fizcalpolicy
06-06-2010, 05:32 AM
Awesome!! Thanks-a-million!