Sumproduct in VBA

[tazz]

Hello all,
I need help in transforming this formula in VBA:
=Sumproduct((data!A2:data!A14000=verify!A2)*(data!B2:data!B14000>=verify!B2)*(data!C2:data!C14000<=verify!C2),(data!D2:data!D14000))
This formula goes in sheet verify cell D2
Thank you for your help.

 

[NARAYANK991]

Hi ,

Have you tried this ?

[d2] = [SUMPRODUCT((data!A2:A14000=verify!A2)*(data!B2:B14000>=verify!B2)*(data!C2:C14000<=verify!C2),(data!D2:D14000))]

Narayan

 

[tazz]

Hi Narayank,
Formula posting by me is working fine. I want a VBA because sometimes formula is loosing the reference cells in data sheet( Because of a VBA code that deletes automatic a number of cells every day), that is why I think VBA will solve the problem.
Thanks

 

[NARAYANK991]

Hi ,

What I posted is VBA !

I thought this code is to be part of some bigger macro ; just put this statement within a VBA macro / procedure.

Narayan

 

[tazz]

So, if I put this into a code for a cmdbutton will work? I tried with a macro recorded and when I tried to execute it I had a error.
I will test it tomorow and I will let you know how it is working.
Thank you

 

[tazz]

Hi Narayan,

I created a module “Sumproduct” using your code and is working fine. Then I tried to execute this module into a CmdButton code and I get this message: “Expect variable or procedure not module”.
This is the code I use for cmdButton:

Private Sub CmdCheck_click()
'insert data to perform SUMPRODUCT
Worksheets("verify").Range("a2").Value = TxtID.Value
Worksheets("verify").Range("b2").Value = TxtStart.Value
Worksheets("verify").Range("c2").Value = TxtEnd.Value
'Module Sumproduct
Sumproduct
'Show working time in Txttime
Txttime.Value = Worksheets("verify").Range("d2").Text
End Sub

And this is the code for the macro"Sumproduct":

Sub Sumproduct()
 
' Sumproduct Macro
 
    Worksheets("verify").Range("D2").Select
    [D2] = [SUMPRODUCT((data!A2:A14000=verify!A2)*(data!B2:B14000>=verify!B2)*(data!C2:C14000<=verify!C2),(data!D2:D14000))]
 
  End Sub

At this point I am lost. I have no idea what is wrong.
Thanks for your help

 

[NARAYANK991]

Hi ,

Firstly , please do not give the same name to your subroutine , as a regular Excel function ; give your subroutine some other name.

Secondly , when you get the error message , is any VBA statement highlighted ? if so , which one is it ?

I assume your CommandButton is named CmdCheck ; is that correct ?

Narayan

 

[tazz]

Hi,
Private sub CmdCheck_click() is yellow and Sumproduct was shown in blue.
My cmdButton is named CmdCheck.

 

[NARAYANK991]

Hi ,

Can you use :

Call sumproduct

instead of just :

sumproduct

Narayan

 

[tazz]

Still the same error.
Should I rename the macro as Sumprod and still use Call Sumproduct?

 

[tazz]

When I do this way Worksheets("verify").Range("D2").Select in macro is yellow

 

[NARAYANK991]

Hi ,

If you rename the subroutine sumprod , then the statement would be :

Call sumprod

You cannot select a range when the sheet is not the active sheet ; rewrite the single statement as follows :

ThisWorkbook.Worksheets("verify").Activate
Activesheet.Range("D2").Select

Of course , you do not need to select the range before you write to it. Just the one statement activating the verify tab is enough.

Narayan

 

[tazz]

Now it's working.
Thank you so much