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

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)

"Michael A" wrote in message
...
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