• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get data between two dates in vba

Hi

The below formula I tried to convert VBA code but I struck how I can tell VBA that between two dates and only in sales data to be extracted.

IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495))

Cell J8: J30495 is sales date
Cell G8: G30495 is having sales and free sample
Cell CF8: CF30495 having the value of Production cost (Need to sum up)
EN1 and EO1 is the start and end date (04-Mar-2018 and 10-Oct-2018)

The code is

Code:
Sub sumdatabetweendates()
   
    Dim ProductionCost As Long
    Dim SalesDate As Long
   
    Worksheets("Master Data").Select
    Range("P1").Select
   
    ProductionCost = Range("CF8:CF30000")
    SalesDate = Range("J8:J30000")
   
    If ActiveCell.Value = Range("EN2") Then
       
        ProductionCost = sum range("CF8:CF30000).value
       
     
 
End Sub
After getting the activecell.value equals to EN2, How do I get CF column sum value, between the dates in J column and only sales in G column?

Expecting your valuable reply.
 
Last edited by a moderator:

AlanSidman

Active Member
Instead of providing us with code that we need to interpret and try to understand, suggest two things.
1. Tell us in simple English what you are attempting to do. We may have a better solution than trying to jury rig yours.
2. Upload a sample workbook, showing what you currently have and a mocked up version (manually prepared) of what you want your solution to look like. We can then test our code solution.

With this information we can better provide you with a viable solution.
 
In the below dropbox link sheet, if cell P1 changes from its dropdown list Then EL1 displays the results by using below formulations.

=IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495)),(IF(P1=EN3,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*T8:T30495)),(IF(P1=EN5,SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*(BY8:BY30495)),IF(P1=EN6,(SUMPRODUCT(CG4:EF4,INDEX('RM Price'!$B$2:$BA$239,MATCH('Master Data'!$EN$1,'RM Price'!$A$2:$A$239),))),(IF(P1=EN7,(SUMPRODUCT((B8:B20496<=EN1)*CE8:CE20496))-(SUMPRODUCT((J8:J20496<=EN1)*BZ8:BZ20496)),(IF(P1=EN4,(SUMPRODUCT((B8:B20496<=EN1)*CF8:CF20496))-(SUMPRODUCT((J8:J20496<=EN1)*CA8:CA20496)),0))))))))))

I would like to change those formulations with VBA Code.

I given a dropbox link as the file is having 3.8MB which is showing more memory while attaching here.


Thanks in advance
 

AlanSidman

Active Member
You continue to explain your issue in terms of an excel formula. I have asked that you explain your issue in Simple English Terms. Think that we are standing in a coffee shop line waiting to place an order. Explain to me (not using excel terms), what you hope to accomplish. Trying to wade through your formula is not what I intend to do. Additionally, we do not need your whole worksheet. A sample of a few records that is representative of your actual file is all we really need. With a before and after scenario. Help us to help you by giving us what we ask for. We are all volunteers here with limited time. We want to be efficient and your presentation is critical for that to happen.
 
You continue to explain your issue in terms of an excel formula. I have asked that you explain your issue in Simple English Terms. Think that we are standing in a coffee shop line waiting to place an order. Explain to me (not using excel terms), what you hope to accomplish. Trying to wade through your formula is not what I intend to do. Additionally, we do not need your whole worksheet. A sample of a few records that is representative of your actual file is all we really need. With a before and after scenario. Help us to help you by giving us what we ask for. We are all volunteers here with limited time. We want to be efficient and your presentation is critical for that to happen.
Dear Alan

If you did not understand my English then leave it. let others reply to me with the solution who can understand. I am not here to learn English.
 

vletm

Excel Ninja
Anbuselvam K
1) Your 'sample' gives some error while it opens
2) Where did You get Your dates? ... Many times end date would be later that start date ... hmm?
Screenshot 2019-04-28 at 22.04.39.png
3) Have You tried Sumifs-function ... anyway shorter? eg. =SUMIFS(CF:CF;G:G;"Sales";J:J;">="&EO1;J:J;"<="&EN1)
ps. It works better after fix those dates!
4) What was that #3 ... if -something ... do it belongs somewhere?
5) What was Your challenge? ... to write that function with VBA? Why?
 

AlanSidman

Active Member
I clearly understand your English. What I don't understand is what you want to happen when you only explain it in terms of Excel and not in terms of business. For now, good bye and good luck.
 

p45cal

Well-Known Member
Dear Alan
If you did not understand my English then leave it. let others reply to me with the solution who can understand. I am not here to learn English.
Alan was not criticising your English.
He was hoping you would explain what you wanted in words because:
1. It is difficult to reverse engineer such a long formula (I had a go)
2. There could be another solution, (a shorter formula, another function, or another way of doing it, something built-in to Excel, with or without a macro) that could give you what you're wanting.

Without it, I will take your advice to Alan.
 
Dear All

Sorry for the missing information in my previous posts.

Here again, I try to explain in a different way.

The below code
 
Dear All

The excel formula is in the cell EL1 and it gives the results depends on cell P1 dropdown list selection. Check the new attached sheet here.

When I select Cell EL1 it is showing this big formula and I want to hide without losing the output results. Then I choose to write a VBA code after searching for some others post.

The code is below for your study. In that code I figured out only Sumproduct function but also I need to get Index Match for (EN6) and sumproduct - sumproduct for (EN4 & EN7)

Also, the below code is showing the formula in the cell EL1 as I wrote that type of code
(Just for checking I put EM7 instead of EL1 in the below code)

Code:
Option Explicit

Sub DropDownListOutPut()
    Worksheets("Master Data").Select
    Range("P1").Select
    If ActiveCell.Value = Range("EN2") Then
        With Range("EM7")
        .Formula = "=SUMPRODUCT(--(J8:J30000<=EN1),--(J8:J30000>=EO1),--(G8:G30000=EL5),CF8:CF30000)"
        .Value = .Value
        End With
    ElseIf ActiveCell.Value = Range("EN3") Then
        With Range("EM7")
        .Formula = "=SUMPRODUCT(--(J8:J30000<=EN1),--(J8:J30000>=EO1),--(G8:G30000=EL5),T8:T30000)"
        .Value = .Value
        End With
    ElseIf ActiveCell.Value = Range("EN5") Then
        With Range("EM7")
        .Formula = "=SUMPRODUCT(--(J8:J30000<=EN1),--(J8:J30000>=EO1),--(G8:G30000=EL5),BY8:BY30000)"
        .Value = .Value
        End With
    End If
End Sub
Finally, I need a code which can give the desired result and without displaying the formulations in the cell EL1.

In the cell, EN1 and EO1 are the dates which I need to consider while extracting the data from Row 8 to 10000.

I hope the information above is enough to understand. Also, i am ready to give more information if you required.

Thanks in advance.

MOD EDIT: Code tags were added.
 

Attachments

Last edited by a moderator:

p45cal

Well-Known Member
When I select Cell EL1 it is showing this big formula and I want to hide without losing the output results.
In the attached, there is a formula in EL1 but you can't see it. This is because I have protected the sheet. Before protecting I:
a) in format cells unlocked all the cells on the sheet
b) for only cell EL1 chose Hidden:
59795

When I later protected the sheet I elected to allow the user to do everything:
59796

This may be all you want?
To see the formula, unprotect the sheet in the Review tab.

If not, then in preparation for another solution, in the same sheet, in cells EM2:EM7 I have (temporarily) taken the components of your long formula. I'd like you to check thoroughly that these are the correct formulae. The one in EM4, for example only looks at one date; is this correct? Do check the others too.
In preparation for your checking I have (temporarily) unhidden the columns that these formulae refer to.

If these are all correct then I need to ask do you do you want the result to appear only in cell EL1 and nowhere else?
 

Attachments

Dear p45cal

Thanks a lot for your prompt reply and the time you have spent for my query.

Your given solution is excellent and it is met my expectations.

However, I am requesting you that can we write some VBA code to get the results in only the cell EL1?

My intention is to learn how to write VBA code for such scenarios as I have many formulations in one cell in the same sheet with different conditions.

Otherwise,

Can you guide me to write the code for only the below two formulations to get the results in the cells?


=SUMPRODUCT((B8:B20496<=EN1)*CF8:CF20496)-SUMPRODUCT((J8:J20496<=EN1)*CA8:CA20496)

=SUMPRODUCT(CG4:EF4,INDEX('RM Price'!$B$2:$BA$239,MATCH($EN$1,'RM Price'!$A$2:$A$239),))

Expecting your valuable reply

Thanks in advance.
 
Anbuselvam K
Did You change Your case or what?
I asked few basic short question from You (#6 Reply) and
... maybe I do not need to try continue.
Dear Vletm

I did not change the case.

I just want to write the VBA code and get the results which those formulas do in the cell EL1. That's all I want.

Sorry If I conveyed my requirements in the wrong way in my previous posts.

Anyway, Really thanks a lot for all your support and times towards my post.
 

vletm

Excel Ninja
Anbuselvam K
Hint:
You should first think what would You write
and after that
You should write what You thought.
If You send misinformation then You won't get You wanted answers.

Below link has good writing how to do:
 
Well Understood.

Can you guide me to write the code?

Otherwise, give me an example file with VBA Code which can give similar output.
 

vletm

Excel Ninja
Anbuselvam K
From Your the first sentence ... I would ask ... really?
Because, seems that You didn't read or understand those.

Your original case was:
IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495))
Now, You're asking ... something different ... except writing ... hmm?

I already offered 'shorter version' of that,
which would be more useful to write even with VBA.
Of course, that didn't include many checking,
which seems to be needed too.
 

p45cal

Well-Known Member
Can you guide me to write the code for only the below two formulations to get the results in the cells?
=SUMPRODUCT((B8:B20496<=EN1)*CF8:CF20496)-SUMPRODUCT((J8:J20496<=EN1)*CA8:CA20496)
=SUMPRODUCT(CG4:EF4,INDEX('RM Price'!$B$2:$BA$239,MATCH($EN$1,'RM Price'!$A$2:$A$239),))
You haven't confirmed that the formuale are correct. Regardless, I can give you some guidelines.
With formulae on a sheet it's best to record your putting that formula in - the recorder does all the hard work of translating the formula into vba code. A good idea, so that sheet names are correct in the code is to start the recorder when another sheet is the active sheet so it can record you going to the correct sheet and range.
If you do that with your long formula you get a recorded macro like:

Sub Macro3()
'

' Macro3 Macro
'

'
Sheets("Master Data").Select
Range("EL1:EM1").Select
ActiveCell.
FormulaR1C1 = _
"=IF(RC[-126]=R[1]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-58]:R[30494]C[-58])),(IF(RC[-126]=R[2]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-122]:R[30494]C[-122])),(IF(RC[-126]=R" & _
"[4]C[2],SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*(R[7]C[-65]:R[30494]C[-65])),IF(RC[-126]=R[5]C[2],(SUMPRODUCT(R[3]C[-57]:R[3]C[-6],INDEX('RM Price'!R2C2:R239C53,MATCH('Master Data'!R1C144,'RM Price'!R2C1:R239C1),))),(IF(RC[-126]=R[6]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[" & _
"2])*R[7]C[-59]:R[20495]C[-59]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-64]:R[20495]C[-64])),(IF(RC[-126]=R[3]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[2])*R[7]C[-58]:R[20495]C[-58]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-63]:R[20495]C[-63])),0))))))))))"

Range("EL2").Select
End Sub

Which you can tidy up by removing all the red highlighted code to leave:
Code:
Sub Macro3()
    Sheets("Master Data").Range("EL1").FormulaR1C1 = _
        "=IF(RC[-126]=R[1]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-58]:R[30494]C[-58])),(IF(RC[-126]=R[2]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-122]:R[30494]C[-122])),(IF(RC[-126]=R" & _
        "[4]C[2],SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*(R[7]C[-65]:R[30494]C[-65])),IF(RC[-126]=R[5]C[2],(SUMPRODUCT(R[3]C[-57]:R[3]C[-6],INDEX('RM Price'!R2C2:R239C53,MATCH('Master Data'!R1C144,'RM Price'!R2C1:R239C1),))),(IF(RC[-126]=R[6]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[" & _
        "2])*R[7]C[-59]:R[20495]C[-59]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-64]:R[20495]C[-64])),(IF(RC[-126]=R[3]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[2])*R[7]C[-58]:R[20495]C[-58]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-63]:R[20495]C[-63])),0))))))))))"
End Sub
Then if you want to replace the result of the formula with a plain value you can alter again to:
Code:
Sub Macro3()
   With Sheets("Master Data").Range("EL1")
      .FormulaR1C1 = _
        "=IF(RC[-126]=R[1]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-58]:R[30494]C[-58])),(IF(RC[-126]=R[2]C[2],(SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*R[7]C[-122]:R[30494]C[-122])),(IF(RC[-126]=R" & _
        "[4]C[2],SUMPRODUCT((R[7]C[-132]:R[30494]C[-132]<=RC[2])*(R[7]C[-132]:R[30494]C[-132]>=RC[3])*(R[7]C[-135]:R[30494]C[-135]=""Sales"")*(R[7]C[-65]:R[30494]C[-65])),IF(RC[-126]=R[5]C[2],(SUMPRODUCT(R[3]C[-57]:R[3]C[-6],INDEX('RM Price'!R2C2:R239C53,MATCH('Master Data'!R1C144,'RM Price'!R2C1:R239C1),))),(IF(RC[-126]=R[6]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[" & _
        "2])*R[7]C[-59]:R[20495]C[-59]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-64]:R[20495]C[-64])),(IF(RC[-126]=R[3]C[2],(SUMPRODUCT((R[7]C[-140]:R[20495]C[-140]<=RC[2])*R[7]C[-58]:R[20495]C[-58]))-(SUMPRODUCT((R[7]C[-132]:R[20495]C[-132]<=RC[2])*R[7]C[-63]:R[20495]C[-63])),0))))))))))"
     .Value = .Value 'this line replaces the formula with a plain value.
   End With
End Sub
With the guide above, you should be able to record and tidy up macros to enter your two SUMPRODUCT formulae yourself.
By using your long formula, you no longer need to check what's in other cells in the code. This is why I asked you to check each sub-portion of the formula.
 
Dear p45cal

Really thanks a lot for your codes and the explanation.

It is working and given the desired results well.

Firstly, I know the macro recording in a simple way like, make borders and change the colours and get the prints.

Indeed I do not know how the R1C1 code be recorded by the macro. of course, I never write R1C1 codes in my Chandoo.org online VBA classes. Maybe later classes I will get this information.

For time being your given code is giving the proper result what I expected.

I thought someone can write the code as like below for this Sumproduct and Index Match formulations. So that I can transfer to my other requirements. (below code for my different requirements given by someone else)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4:F4")) Is Nothing Then
        If Target.Count > 3 Then Exit Sub
        Range("A7:E16").Value = ""
        pName = Range("C4").Value
        nOrig = Range("E4").Value
        If pName = "" Or nOrig = "" Then Exit Sub
        '
        existe = False
        k = 7
        Set h = Sheets("Formulation")
        Set r = h.Columns("A")
        Set b = r.Find(pName, LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                'detalle
                If LCase(h.Cells(b.Row, "B").Value) = LCase(nOrig) Then
                    existe = True
                    uc = h.Cells(1, Columns.Count).End(xlToLeft).Column
                    For j = 3 To uc
                        If h.Cells(b.Row, j).Value <> "" Then
                            Cells(k, "A").Value = h.Cells(1, j).Value
                            Cells(k, "B").Value = h.Cells(3, j).Value
                            Cells(k, "E").Value = h.Cells(b.Row, j).Value
                            k = k + 1
                        End If
                    Next
                    Exit Do
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        Else
            MsgBox "Prodcut not exists"
        End If
        If existe = False Then
            MsgBox "Relation Prodcut - Origin not exists"
        End If
    End If
End Sub
Please note that my intention is to learn to write the code for my own. Because I have many scenarios have to be implemented by VBA code which I cannot post and get the answers all the time.

Really I thought I will get like above code to replace the Sumproduct and Index match formulas.

But, Anyway really thanks again for your support.
 
Last edited by a moderator:
Dear All

Just check the below link sheet where I got all the results except when I select E6 (Raw materials Stock Value) value in the cell P1 dropdown list, as its required VBA code for index match formula.

below code is for your information and help me with the code between Line 109 to Line 111.



Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P1")) Is Nothing Then
    Dim J As Range, G As Range, CF As Range, T As Range, BY As Range, B As Range, CA As Range, CE As Range, BZ As Range
    Dim Low As Date, High As Date
    Dim DataJ, DataG, DataCF, DataT, DataBY, DataB, DataCA, DataCE, DataBZ
    Dim i As Long
    Dim Sum As Double, Sum1 As Double, Sum2 As Double
        If ActiveCell.Value = Range("EN2") Then
            'Get the dates
            Low = Range("EO1").Value
            High = Range("EN1").Value
            'Refer to the used cells in column J
            Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
            'Same size in column G
            Set G = Intersect(Columns("G"), J.EntireRow)
            'Same size in column CF
            Set CF = Intersect(Columns("CF"), J.EntireRow)
            
            'Read in all data
            DataJ = J.Value
            DataG = G.Value
            DataCF = CF.Value
            
            'Process the SUMPRODUCT
            For i = 1 To UBound(DataJ)
              If DataJ(i, 1) >= Low And DataJ(i, 1) <= High And DataG(i, 1) = "Sales" Then
                Sum = Sum + DataCF(i, 1)
                Range("EL7") = Sum
              End If
              Next
    ElseIf ActiveCell.Value = Range("EN3") Then
            'Get the dates
            Low = Range("EO1").Value
            High = Range("EN1").Value
            'Refer to the used cells in column J
            Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
            'Same size in column G
            Set G = Intersect(Columns("G"), J.EntireRow)
            'Same size in column CF
            Set T = Intersect(Columns("T"), J.EntireRow)
            
            'Read in all data
            DataJ = J.Value
            DataG = G.Value
            DataT = T.Value
            
            'Process the SUMPRODUCT
            For i = 1 To UBound(DataJ)
              If DataJ(i, 1) >= Low And DataJ(i, 1) <= High And DataG(i, 1) = "Sales" Then
                Sum = Sum + DataT(i, 1)
                Range("EL7") = Sum
              End If
              Next
     ElseIf ActiveCell.Value = Range("EN4") Then
        'Get the dates
            Low = Range("EO1").Value
            High = Range("EN1").Value
            'Refer to the used cells in column J
            Set B = Range("B8", Range("B" & Rows.Count).End(xlUp))
            Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
            'Same size in column CF
            Set CF = Intersect(Columns("CF"), B.EntireRow)
            Set CA = Intersect(Columns("CA"), J.EntireRow)
            
            'Read in all data
            DataB = B.Value
            DataCF = CF.Value
            DataJ = J.Value
            DataCA = CA.Value
            
            'Process the SUMPRODUCT
            For i = 1 To UBound(DataB)
              If DataB(i, 1) <= High Then
                Sum1 = Sum1 + DataCF(i, 1)
              End If
              Next
            For i = 1 To UBound(DataJ)
              If DataJ(i, 1) <= High Then
                Sum2 = Sum2 + DataCA(i, 1)
                Sum = Sum1 - Sum2
                Range("EL7") = Sum
               End If
              Next
    ElseIf ActiveCell.Value = Range("EN5") Then
        'Get the dates
            Low = Range("EO1").Value
            High = Range("EN1").Value
            'Refer to the used cells in column J
            Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
            'Same size in column G
            Set G = Intersect(Columns("G"), J.EntireRow)
            'Same size in column CF
            Set BY = Intersect(Columns("BY"), J.EntireRow)
            
            'Read in all data
            DataJ = J.Value
            DataG = G.Value
            DataBY = BY.Value
            
            'Process the SUMPRODUCT
            For i = 1 To UBound(DataJ)
              If DataJ(i, 1) >= Low And DataJ(i, 1) <= High And DataG(i, 1) = "Sales" Then
                Sum = Sum + DataBY(i, 1)
                Range("EL7") = Sum
              End If
              Next
    ElseIf ActiveCell.Value = Range("EN6") Then
            MsgBox ("Need to add the VBA Code to get results")
    ElseIf ActiveCell.Value = Range("EN7") Then
            Low = Range("EO1").Value
            High = Range("EN1").Value
            'Refer to the used cells in column J
            Set B = Range("B8", Range("B" & Rows.Count).End(xlUp))
            Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
            'Same size in column CF
            Set CE = Intersect(Columns("CE"), B.EntireRow)
            Set BZ = Intersect(Columns("BZ"), J.EntireRow)
            
            'Read in all data
            DataB = B.Value
            DataCE = CE.Value
            DataJ = J.Value
            DataBZ = BZ.Value
            
            'Process the SUMPRODUCT
            For i = 1 To UBound(DataB)
              If DataB(i, 1) <= High Then
                Sum1 = Sum1 + DataCE(i, 1)
              End If
              Next
            For i = 1 To UBound(DataJ)
              If DataJ(i, 1) <= High Then
                Sum2 = Sum2 + DataBZ(i, 1)
                Sum = Sum1 - Sum2
                Range("EL7") = Sum
               End If
              Next
    End If
    End If
End Sub
 

vletm

Excel Ninja
Anbuselvam K
You should reread next page:
 
Top