Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb help, convert excel formula to vb formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb help, convert excel formula to vb formula
As before.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Michael A" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
How to convert a static formula to dynamic formula ? | Excel Worksheet Functions | |||
how to convert a formula into text in order to display the formula | Excel Discussion (Misc queries) | |||
Convert Excel Formula to VBA | Excel Programming | |||
How can I convert a formula to a value in excel? | Excel Discussion (Misc queries) |