vb help, convert excel formula to vb formula

vb help, convert excel formula to vb formula

Post by TWljaGFlbC » Sun, 08 Jan 2006 19:16:01


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
 
 
 

vb help, convert excel formula to vb formula

Post by Bob Philli » Sun, 08 Jan 2006 20:31:46

As before.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)



been
=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))
B