Solved: Search for cell values then sum scell to......

[fizcalpolicy]

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]

Welcome to VBAX

[VBA]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
[/VBA]

[Bob Phillips]

You could try an array formula, such as

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

[fizcalpolicy]

Thanks-a-million!!

[fizcalpolicy]

Awesome!! Thanks-a-million!