Sum Cells if criteria is met

[sharky12345]

I'm trying to find a way using VBA to sum the total values, (which are numbers), in a range but only if the adjacent cell matches a specific criteria.

I have this but it only gives me the first value found, not the total;

For Each Cell In SourceFile.Sheets("Data").Range("A7:A30")If Cell <> 0 Then
If InStr(Cell, "Constant") > 0 Then
Total = Cell.Offset(0, 1).Value
MsgBox Total
End If
End If
Next

Can anyone show me where I am going wrong please?

 

[Joe4]

Why not use the SUMIF function?
You can use it in VBA like:

Application.WorksheetFunction.SumIf(...)

 

[sharky12345]

Thanks!!!