software

Hello,

I am trying to get this formula to work.. and Im not sure where to go from
here. Could someone please help? Thanks in advance, this community has been
very great.

Im trying to convert this excel formula to a vb formula:
=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND(quot;cxlquot;,H1:H3000))))

I need it to count how many times it finds quot;CXLquot; in the H column when the B
column matches up with the user input.

code:

Sub MAM()
Dim nStuff As String
Dim ThisSheet As String
Dim j As Long

ThisSheet = ActiveSheet.Name

nStuff = InputBox _
(quot;What date is this for? I.E 1/1/2006,1/5/2006 etc.quot;)

Application.ScreenUpdating = False
Workbooks.Open Filename:=quot;\\server1\sharedfile\mam2006.xlsquot;

With Worksheets(quot;Jan 06quot;)
'Now working in the MAM Sheet
j = Application.SumProduct(--(.Columns(2) = nStuff),
--(IsNumber(Find(quot;cxlquot;, .Columns(8)))))

End With

Workbooks(quot;January.xlsquot;). _
Worksheets(ThisSheet).Range(quot;B45quot;).Value = j

ActiveWorkbook.Close False
Application.ScreenUpdating = True

End Sub
See response in other thread. Don't just fire off new threads, it is not
good etiquette and wastes people's time.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Michael Aquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I am trying to get this formula to work.. and Im not sure where to go from
gt; here. Could someone please help? Thanks in advance, this community has
been
gt; very great.
gt;
gt; Im trying to convert this excel formula to a vb formula:
gt;
=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND(quot;cxlquot;,H1:H3000))))
gt;
gt; I need it to count how many times it finds quot;CXLquot; in the H column when the
B
gt; column matches up with the user input.
gt;
gt; code:
gt;
gt; Sub MAM()
gt; Dim nStuff As String
gt; Dim ThisSheet As String
gt; Dim j As Long
gt;
gt; ThisSheet = ActiveSheet.Name
gt;
gt; nStuff = InputBox _
gt; (quot;What date is this for? I.E 1/1/2006,1/5/2006 etc.quot;)
gt;
gt; Application.ScreenUpdating = False
gt; Workbooks.Open Filename:=quot;\\server1\sharedfile\mam2006.xlsquot;
gt;
gt; With Worksheets(quot;Jan 06quot;)
gt; 'Now working in the MAM Sheet
gt; j = Application.SumProduct(--(.Columns(2) = nStuff),
gt; --(IsNumber(Find(quot;cxlquot;, .Columns(8)))))
gt;
gt; End With
gt;
gt; Workbooks(quot;January.xlsquot;). _
gt; Worksheets(ThisSheet).Range(quot;B45quot;).Value = j
gt;
gt; ActiveWorkbook.Close False
gt; Application.ScreenUpdating = True
gt;
gt; End Sub
gt;
gt;