need help converting worksheet formula to vba

[Zygoid]

right now i have this formula in cell A75. It searches for "1" and i
found shows "1" in cell A75

=IF(SUMPRODUCT(ISNUMBER(SEARCH("1",$A$12:$A$72))+0)>0=TRUE,1,0)

I have this formula in B75. it sums all cells in col. I that has the
in col. A

=SUMIF($A$13:$A$72,A75,$I$13:$I$72)

I am trying to convert this to VBA, this is what I have tried.

Set SourceWB = Workbooks("book1.xls")
With SourceWB.Sheets("sheet1")

I am getting "Sub or function not defined" and "search" is highlited.

Range("a75")
Application.WorksheetFunction.SumProduct(IsNumber(Search("1"
Workbooks("book1.xls").Worksheets("sheet1").Range("A12:A72")) + 0) >
= True, 1, 0)


I would apprciate any help. "Thanks" in advance

 

[Frank Kabel]

Hi
you can't use this kind of SUMPRODUCT syntax within VBA.
One workaround: Use Evaluate within VBA.