vb help, convert excel formula to vb formula

[Michael A]

Sorry for the duplicate. I posted this under the wrong cattegory.

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("cxl",H1:H3000))))

I need it to count how many times it finds "CXL" 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 _
("What date is this for? I.E 1/1/2006,1/5/2006 etc.")

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\server1\sharedfile\mam2006.xls"

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

End With

Workbooks("January.xls"). _
Worksheets(ThisSheet).Range("B45").Value = j

ActiveWorkbook.Close False
Application.ScreenUpdating = True

End Sub

[Bob Phillips]

As before.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" <[email protected]> wrote in message
news:[email protected]...
> Sorry for the duplicate. I posted this under the wrong cattegory.
>
> 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("cxl",H1:H3000))))
>
> I need it to count how many times it finds "CXL" 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 _
> ("What date is this for? I.E 1/1/2006,1/5/2006 etc.")
>
> Application.ScreenUpdating = False
> Workbooks.Open Filename:="\\server1\sharedfile\mam2006.xls"
>
> With Worksheets("Jan 06")
> 'Now working in the MAM Sheet
> j = Application.SumProduct(--(.Columns(2) = nStuff),
> --(IsNumber(Find("cxl", .Columns(8)))))
>
> End With
>
> Workbooks("January.xls"). _
> Worksheets(ThisSheet).Range("B45").Value = j
>
> ActiveWorkbook.Close False
> Application.ScreenUpdating = True
>
> End Sub
>
>
>