sumproduct in vba

[cmefly]

hi,

i'm trying to develop a code that searches column K for all occurrences of a certain string and adds up the associated values found in column I.

example: look for "def" within column K and and up column I

column i column K
1 abcdefghi
2 defghij
3 123defghijklm
4 _def_123
5 af;lkeion

any ideas on where to start????

 

[cmefly]

i've got this but it's not working....HELP PLZ!

Sub testbi()
Dim y As Integer
y = Sheets("14. Analysis").Range("Z1").Value
x = 0

For Each cell In Sheets("4. schedule").Range("K49:k78").Cells
    If InStr(1, cell, y & "def") = 0 Then
    x = x + Sheets("4. schedule").Range("I" & cell.Row).Value
    End If
Next cell


Range("B19") = x



End Sub

 

[jim may]

Excel Workbook
	I	J	K	L
1	111.00		abcdefghi	1,110.00
2	222.00		defghij
3	333.00		123defghijklm
4	444.00		_def_123
5	555.00		af;lkeion
Sheet1

Excel 2007

Cell Formulas
Range	Formula
L1	=SUMIF($K$1:$K$5,"*def*",I1:I5)

 

[jim may]

The code equivalent is:

Sub Macro1()
    Range("L1").FormulaR1C1 = "=SUMIF(C11,""*def*"",C9)"  '(modified for full columns)
End Sub