News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Error in VBA code – Need solution

    Posted on WSanbuselvam11 Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Error in VBA code – Need solution

    Viewing 19 reply threads
    • Author
      Posts
      • #1858245 Reply
        WSanbuselvam11
        AskWoody Lounger

        Dear All

        In the below linked excel file has a VBA code which can run when we change the Cell “P1” value. The expected result will be in “EL7”

        When I change it is showing type mismatch error in the VBA Code

        Can anyone help me out to change the VBA code?

        https://www.dropbox.com/s/6kpdyen2f4oom8o/Ask%20woody.xlsm?dl=0

        Thanks in Advance

      • #1858366 Reply
        Paul T
        AskWoody MVP

        Help us to help you by giving us the full error message, particularly details of the call that fails.

        cheers, Paul

      • #1858396 Reply
        WSanbuselvam11
        AskWoody Lounger

        Hi

        Please check the below link file,

        In the cell “P1” drop-down list if we change

        then the

        cell “EL7” will display the results by VBA code.

        Also, the cell “EL1” will display similar results by excel formulas like sumproduct and index match.

        When I select “Raw Materials Stock Value” in the Drop-Down List the VBA code is not giving desired results.

        https://www.dropbox.com/s/ibt2oxzwumvjuon/Ask%20woody1.xlsm?dl=0

        The full VBA code is here for your check.

        Option Explicit

        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim J As Range, G As Range, Where As Range, Colu As Range, Fndrow As Range
        Dim Low As Date, High As Date
        Dim Dates, Types, Values
        Dim i As Long
        Dim Sum As Double, Sum1 As Double, Sum2 As Double

        ‘Only if P1 changes
        If Intersect(Target, Range(“P1”)) Is Nothing Then Exit Sub

        ‘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)
        ‘Read in all data
        Dates = J.Value
        Types = G.Value

        Select Case Target.Value
        Case Range(“EN2”).Value
        ‘Same size in column CF
        Set Where = Intersect(Columns(“CF”), J.EntireRow)
        ‘Read in all values
        Values = Where.Value
        ‘Process the SUMPRODUCT
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN3”).Value
        Set Where = Intersect(Columns(“T”), J.EntireRow)
        Values = Where.Value
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN4”).Value
        Set Where = Intersect(Columns(“B”), J.EntireRow)
        Dates = Where.Value
        Set Where = Intersect(Columns(“CF”), J.EntireRow)
        Values = Where.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum1 = Sum1 + Values(i, 1)
        End If
        Next

        Set Where = Intersect(Columns(“CA”), J.EntireRow)
        Values = Where.Value
        Dates = J.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum2 = Sum2 + Values(i, 1)
        End If
        Next
        Sum = Sum1 – Sum2

        Case Range(“EN5”).Value
        Set Where = Intersect(Columns(“BY”), J.EntireRow)
        Values = Where.Value
        ‘Process the SUMPRODUCT
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN6”).Value
        For Each Colu In Sheet2.Range(“A2”, Sheet2.Range(“A” & Rows.Count).End(xlUp))
        If Colu.Value = High Then
        Set Fndrow = Colu
        Exit For
        ElseIf Colu.Value > High Then
        Set Fndrow = Colu.Offset(-1)
        Exit For
        End If
        Next Colu
        If Fndrow Is Nothing Then
        Set Fndrow = Sheet2.Range(“A” & Rows.Count).End(xlUp)
        End If
        For J = 85 To 136
        For i = 2 To 53
        Sum = Sum + Cells(4, i).Value * Fndrow(1, i).Value
        Next i
        Next
        Sum = Sum / 52
        Case Range(“EN7”).Value
        Set Where = Intersect(Columns(“B”), J.EntireRow)
        Dates = Where.Value
        Set Where = Intersect(Columns(“CE”), J.EntireRow)
        Values = Where.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum1 = Sum1 + Values(i, 1)
        End If
        Next

        Set Where = Intersect(Columns(“BZ”), J.EntireRow)
        Values = Where.Value
        Dates = J.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum2 = Sum2 + Values(i, 1)
        End If
        Next
        Sum = Sum1 – Sum2
        End Select

        ‘Events off, otherwise we call ourself
        Application.EnableEvents = False
        ‘Write the sum into the sheet
        Range(“EL7”) = Sum
        ‘Events on
        Application.EnableEvents = True
        End Sub

      • #1858397 Reply
        WSanbuselvam11
        AskWoody Lounger

        Note:

        In my previous post linked file, I have removed the code for case range(“EN6”) in the Module and added a MsgBox because that case range has some issues while calling the code.

      • #1858480 Reply
        Paul T
        AskWoody MVP

        “Type mismatch” or the wrong values?

        cheers, Paul

      • #1858544 Reply
        anonymous
        Guest

        The above-pasted code will result in type mismatch error on line 92 (Check my first post link)

        For Case Range (“EN6”) gives proper result in the below code

        Sub indexmatch()
        Dim colu As Range, FndRow As Range
        Dim High As Date
        Dim i As Long
        Dim Sum As Double

        High = Range(“EN1”).Value
        For Each colu In Sheet2.Range(“A2”, Sheet2.Range(“A” & Rows.Count).End(xlUp))
        If colu.Value = High Then
        Set FndRow = colu
        Exit For
        ElseIf colu.Value > High Then
        Set FndRow = colu.Offset(-1)
        Exit For
        End If
        Next colu
        If FndRow Is Nothing Then
        Set FndRow = Sheet2.Range(“A” & Rows.Count).End(xlUp)
        End If
        For J = 85 To 136
        For i = 2 To 53
        Sum = Sum + Cells(4, J).Value * FndRow(1, i).Value
        Next i
        Next
        Range(“EN10”) = Sum / 52
        End Sub

         

        But, When i transfer this code in to case range(“EN6”).Value it is showing type mismatch error

      • #1858806 Reply
        mngerhold
        AskWoody Lounger

        For J = 85 To 136

        For i = 2 To 53

        Sum = Sum + Cells(4, i).Value * Fndrow(1, i).Value

        Next i

        Next

        You declare J as a range, then use it as a loop counter.  I renamed the counter to xx so I could continue: it looped along the columns (i) in row 4, until it got to cell (4,19) <S4> whereupon it had trouble doing arithmetic on the text value!  Runtime error 13: type mismatch.  I suggest you sort this then come back…

        PS I had great difficulty (don’t know why) getting the code to actually run on a worksheet change – that may be my settings.

        Have fun, Martin

        • This reply was modified 1 year, 1 month ago by mngerhold.
        1 user thanked author for this post.
      • #1859736 Reply
        WSanbuselvam11
        AskWoody Lounger

        Hi

        https://www.dropbox.com/s/dzky5n2js9vvw6z/Ask%20woody2.xlsm?dl=0

        The above link I have changed P instead of J in the For loop. Now there is no error while run the code.

        But, The cell EL1 is showing desired results by excel formula when I select the cell P1 drop-down list “Raw Materials Stock Value” by using below formula.

        (IF(P1=EN6,(SUMPRODUCT(CG4:EF4,INDEX(‘RM Price’!$B$2:$BA$239,MATCH(‘Master Data’!$EN$1,’RM Price’!$A$2:$A$239))

         

        The cell EL7 is showing different values by the VBA code. There is something wrong in the For Loop calculation again.

        Can you please check and correct the code to get the results in the cell EL7 as like EL1?

        Thanks in advance.

        • #1860543 Reply
          GoneToPlaid
          AskWoody Plus

          All loop variables should always be declared as integers. Yet you declare J as Range and i as long. In particular, you are using setting J as a Range, and yet later (line 92) you are using J as a loop variable. Why not declare as either Integers or Long some specific variables which are only to be used for loops, such as loopI and loopJ?

          It is always good to declare variables with names which reflect how the variables will be used. I declare variables which are only used within a given subroutine with names which start with a lower case letter. I declare variables which may be updated by other subroutines with names which start with a capital letter. I declare all global variables which might be updated by any subroutine with names which are in all capitals. Using these naming conventions allows me to keep track both how and when variables might be updated.

          I also use code to make sure that I can read a variable (of any numerical type) and convert it to the proper type for processing. Here is an example in which I convert X1 to a string, then I use Val to get the numerical value of the string, and then I convert the value to Long since Y1 was declared as Long:

          Y1 = CLng(Val(CStr(X1)))

          Yeah, messy, but it assures that whatever numerical type X1 is, it does get processed and converted to Long (or whatever other variable type one desires by changing CLng to another variable type such as CInt or CDbl). I do this in order to avoid any Type Mismatch errors if the type values in a spreadsheet were inadvertently changed to another type. In other words and when working with spreadsheets, you want to make sure that you convert spreadsheet cell values to the correct type when processing those cell values. I have a feeling that some cell values in columns within your spreadsheet are not all of the same variable type. This is why is best to use the method which I have shown above, in order to make sure that the cell value is converted to the correct type when processing spreadsheet cell values.

          Your code is hard to read since it isn’t properly indented, and since it has very little documentation.

           

          1 user thanked author for this post.
      • #1859821 Reply
        mngerhold
        AskWoody Lounger

        Well, maybe I can, but that would mean spending time trying to understand what you are trying to calculate, and you are better placed than I to do that.  I may have a look at it later, but you could try explaining what the sums are for.

        But are you still doing sums on the contents of cell S4, because they will always fail?

        • This reply was modified 1 year, 1 month ago by mngerhold.
        2 users thanked author for this post.
        • #1859832 Reply
          WSanbuselvam11
          AskWoody Lounger

          For P = 85 To 136
          For i = 2 To 53
          Sum = Sum + Cells(4, P).Value * FndRow(1, i).Value
          Next i
          Next
          Sum = Sum / 52

           

          For P = 85 To 136 (Sheet Master data row number 4 and the column from CG to EF)
          For i = 2 To 53 (Sheet RM Price row number is FndRow and the column from B to BA

          There is no cell S4 in the calculation.

      • #1859829 Reply
        WSanbuselvam11
        AskWoody Lounger

        Select the cell E1 and you can see the entire formula and how the results are coming depends on the cell P1 drop-down selection.

        Likewise, I converted those excel formulas to VBA code to get the same results in the cell EL7 through VBA Code.

        In the drop-down list, I have totally 6 different things. For each selection, there is a different result displayed in the cell EL1 by excel formula as well as in the cell EL7 by VBA code.

        When I select “Raw materials stock value” in the drop-down then the VBA result is not matching the excel formula result.

        I need to get the cell EL1(Excel Formula) result in the cell EL7(VBA) too…

         

        Hope the above information explained my query. Still, if you need more information I am here to provide

        I need a solution

        Thanks in Advance.

      • #1860117 Reply
        doriel
        AskWoody Lounger

        Dunno why you use range method for single cell? Is this macro recorded or hand-written? This range can cause your problems.

        Better approach is to define whole thing. I use multiple files with my macros, so I prefer to define it the “hard way”.
        Dim sourcefile As Workbook

        Set sourcefile = ThisWorkbook


        For…
        If sourcefile.Sheets(1).Cells(i, 5).Text <> “” Then

        OR
        If sourcefile.Sheets(1).Cells(i, 5).Value=5 Then

        It is much better to handle numbers as values, of text as a text. this may help

        Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

        HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

        1 user thanked author for this post.
      • #1860129 Reply
        anonymous
        Guest

        it was not a single cell totally 52 cells in both sheets need to multiply each and sum is required. like the sumproduct formula.

        Also, I do not have much experience in the VBA code writing I am just a beginner

        this code I got it from forums like here.

        If you could change the code and post here would be very helpful for me.

        Thanks in advance.

      • #1860145 Reply
        mngerhold
        AskWoody Lounger

        I think I give up: you declare FndRow as a range, then set it to colu.value (which = High which is the contents of cell EN1, ie ’03/06/2015′), then later refer to FndRow(1,i) where i runs from 2 to 53.  I don’t understand how Excel treats this offset into a single item range (it may do something clever), but the values are 10 for the first few, followed by zeroes.  This in no way corresponds with your Excel formula in cell EN1, although that does look for a match with cell EN1.  Where is the equivalent of the ‘match’ in the vba code?

        Cell S4 was where the previous code bombed out because you were doing arithmetic on a text value.  I can see what you are doing (sort of), but its too much trouble to unravel the sumproduct formula without more info from you.

        3 users thanked author for this post.
      • #1860156 Reply
        anonymous
        Guest

        I need to convert the below excel formula to the VBA code

        (IF(P1=EN6,(SUMPRODUCT(CG4:EF4,INDEX(‘RM Price’!$B$2:$BA$239,MATCH(‘Master Data’!$EN$1,’RM Price’!$A$2:$A$239))

        I think you can understand what the formula does in the cell EL1.

        if possible please rewrite the code as per your idea.

        The same result of cell EL1 I need it in the cell EL7 by VBA code without using any applications.function Sumproduct, index and match in the VBA Code

        I would like to learn VBA code with your valuable supports.

        If someone can give the code it will be a great help for me.

        • #1860906 Reply
          doriel
          AskWoody Lounger

          You can copy your formulas to another cells in VBA, if you have already created some.



          copythis = Sheets(1).Cells(5, 1).formula
          Sheets(1).Cells(6, 1).formula = copythis

          It also works with range method:


          copythat = Range(“B15”).Formula

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

          • This reply was modified 1 year, 1 month ago by doriel. Reason: range formula
          1 user thanked author for this post.
      • #1860297 Reply
        Paul T
        AskWoody MVP

        I don’t think it’s appropriate to ask us to write what can be done with formula as code. Using the formulas in your VBA is the best solution.

        cheers, Paul

        2 users thanked author for this post.
      • #1861024 Reply
        zeddy
        AskWoody_MVP

        Hi

        When you change the dropdown value in cell [P1], you want to run vba code to calculate the relevant value that corresponds to your dropdown selection, and display the result.

        This may be an exercise in learning vba coding, but perhaps you should remember that Excel is more efficient in calculating values in the spreadsheet, rather than calculating values in vba loops.

        I had a quick look at your file, and made a few tweaks. First, I got rid of all the vba code.

        When you select a value from the dropdown in cell [P1], the calculated value in AE Dirhams is shown alongside. See attached file..

        zeddy

        A day without sunshine is like night

        Ask-woody-anbuselvam-rz1

         

        Attachments:
        1 user thanked author for this post.
      • #1861027 Reply
        zeddy
        AskWoody_MVP

        Hi

        In my attached file (previous post), I changed your formula in cell [EL1]  from

        =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))))))))))

        to..

        =INDEX(EL2:EL7,MATCH(P1,EN2:EN7,0))

        zeddy

         

        • This reply was modified 1 year, 1 month ago by zeddy.
        2 users thanked author for this post.
      • #1861034 Reply
        anonymous
        Guest

        Dear Zeddy and Doriel

        Thanks a lot for your valuable time towards my post.

        I accept that excel is faster than VBA in terms of calculating values.

        My intention is to convert some long and complex excel formulas to VBA code for the purpose of learning.

        Anyway, once again thanks for all your efforts.

        For your information,

        I will keep search in other sources to fine tune the VBA code to get the desired results.

        Sincerely Yours | Anbuselvam K

        • #1861607 Reply
          zeddy
          AskWoody_MVP

          Hi

          OK, if you want to use your vba code to generate the results when you make the dropdown selection change in cell [P1], you fix the initial loop counter problem for..
          Case Range(“EN6”).Value
          ..by defining a variable P As Long, and then using loop counter P instead of J, as posted above..
          For P = 85 To 136
          For i = 2 To 53
          Sum = Sum + Cells(4, P).Value * FndRow(1, i).Value
          ..

          Now, the most important part is to make sure you have your date cells defined in cells [EN1] and [EO1] in the correct order!
          The vba code uses..
          ‘Get the dates
          Low = Range(“EO1”).Value
          High = Range(“EN1”).Value
          ..but in the dropbox file you have these start and end dates in the wrong order.
          You either switch the dates around in the cells i.e. put 1-Jan-18 in cell [EN1] and put 3-Jun-15 in cell [EO1] (so that the Low date is the earliest date, and the High date is the later date.
          Or, you can leave the dates as they are in the worksheet ( [EN1] = 3-Jun-15 ; [EO1] = 1-Jan-18 ) and change the vba code to..
          ‘Get the dates
          Low = Range(“EN1”).Value
          High = Range(“EO1”).Value

          zeddy
          Excel Rapid Tragedy Response Team

      • #1862042 Reply
        WSanbuselvam11
        AskWoody Lounger

        Dear Zeddy

        Thanks for your valuable reply.

        After observing all the codes and the results, I found the difficulties in the code writing is one sheet values is in 85 to 136 and another one-sheet values from 2 to 53

        this is what I required For P and For i to call the two values from two sheets.

        Now I have inserted dummy random values in the second sheet column 2 to 84 So that both sheets have the values in column 85 to 136.

        Please find the link and modified code below for your information. it is working fine now.

        Also, the start and end dates are in orders now.

        I will keep this code until getting the proper one.

        https://www.dropbox.com/s/sh5lqu6lknsl1be/Ask%20woody4.xlsm?dl=0

         

        Code:

        Option Explicit

        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim J As Range, G As Range, Where As Range, colu As Range, FndRow As Range
        Dim Low As Date, High As Date
        Dim Dates, Types, Values
        Dim i As Long
        Dim Sum As Double, Sum1 As Double, Sum2 As Double

        ‘Only if P1 changes
        If Intersect(Target, Range(“P1”)) Is Nothing Then Exit Sub

        ‘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)
        ‘Read in all data
        Dates = J.Value
        Types = G.Value

        Select Case Target.Value
        Case Range(“EN2”).Value
        ‘Same size in column CF
        Set Where = Intersect(Columns(“CF”), J.EntireRow)
        ‘Read in all values
        Values = Where.Value
        ‘Process the SUMPRODUCT
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN3”).Value
        Set Where = Intersect(Columns(“T”), J.EntireRow)
        Values = Where.Value
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN4”).Value
        Set Where = Intersect(Columns(“B”), J.EntireRow)
        Dates = Where.Value
        Set Where = Intersect(Columns(“CF”), J.EntireRow)
        Values = Where.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum1 = Sum1 + Values(i, 1)
        End If
        Next

        Set Where = Intersect(Columns(“CA”), J.EntireRow)
        Values = Where.Value
        Dates = J.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum2 = Sum2 + Values(i, 1)
        End If
        Next
        Sum = Sum1 – Sum2

        Case Range(“EN5”).Value
        Set Where = Intersect(Columns(“BY”), J.EntireRow)
        Values = Where.Value
        ‘Process the SUMPRODUCT
        For i = 1 To UBound(Dates)
        If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = “Sales” Then
        Sum = Sum + Values(i, 1)
        End If
        Next

        Case Range(“EN6”).Value
        For Each colu In Sheet2.Range(“A2”, Sheet2.Range(“A” & Rows.Count).End(xlUp))
        If colu.Value = High Then
        Set FndRow = colu
        Exit For
        ElseIf colu.Value > High Then
        Set FndRow = colu.Offset(-1)
        Exit For
        End If
        Next colu
        If FndRow Is Nothing Then
        Set FndRow = Sheet2.Range(“A” & Rows.Count).End(xlUp)
        End If
        For i = 85 To 136
        Sum = Sum + Cells(4, i).Value * FndRow(1, i).Value
        Next i
        Case Range(“EN7”).Value
        Set Where = Intersect(Columns(“B”), J.EntireRow)
        Dates = Where.Value
        Set Where = Intersect(Columns(“CE”), J.EntireRow)
        Values = Where.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum1 = Sum1 + Values(i, 1)
        End If
        Next

        Set Where = Intersect(Columns(“BZ”), J.EntireRow)
        Values = Where.Value
        Dates = J.Value

        For i = 1 To UBound(Dates)
        If Dates(i, 1) <= High Then
        Sum2 = Sum2 + Values(i, 1)
        End If
        Next
        Sum = Sum1 – Sum2
        End Select

        ‘Events off, otherwise we call ourself
        Application.EnableEvents = False
        ‘Write the sum into the sheet
        Range(“EL7”) = Sum
        ‘Events on
        Application.EnableEvents = True
        End Sub

        • #1862212 Reply
          zeddy
          AskWoody_MVP

          Hi

          I’m having a problem downloading your latest dropbox file.

          zeddy

        • #1862227 Reply
          zeddy
          AskWoody_MVP

          Hi

          For VBA training, you might want to consider using Filters for your data records.
          These can be very useful.

          I cannot post an Excel file with vba code here, so I have attached an .xlsx file without the vba code. The attached .xlsx filesize is approx. 1,638KB
          You could insert a Module in the VBA Editor, and copy the code below into the file.
          Then save the file as an Excel binary .xlsb format.
          This allows vba code to be used in the file. It also makes the filesize much smaller.
          After adding the vba code and saving as .xlsb format, the filesize should be around 669KB (that’s almost 1,000KB smaller! ; NOTE: smaller files load faster on a network system)

          The code is:

          Sub turnFiltersON()
          
          'Get the dates
          Low = [EO1]
          High = [EN1]
          
          r = Cells(Rows.Count, "J").End(xlUp).Row
          Range("A7:CE" & r).Name = "block1"
          [block1].AutoFilter
          
          [block1].AutoFilter Field:=[J1].Column, _
              Criteria1:=">=" & Low, Operator:=xlAnd, Criteria2:="<=" & High
          
          [block1].AutoFilter Field:=[G1].Column, Criteria1:="Sales"
          
          End Sub
          
          Sub turnFiltersOFF()
          
          If ActiveSheet.AutoFilterMode Then
          ActiveSheet.AutoFilterMode = False
          End If
          
          End Sub
          

          The attached file shows the [Master] sheet after running the turnFiltersON() routine.

          With this file, you can then use the dropdown filters to drill down your data records, for example, you could select a particular Customer using the filter dropdown in cell [L7], or select a particular location using filter dropdown in cell [M7].

          Note that the SUBTOTAL formulas in top rows 1:5 will then show relevant results for that column.
          For example, for column [CF] Total Production Cost, the following will show the results for the currently selected filter dropdown choices..
          Total:
          [CF1] =SUBTOTAL(9,CF$7:CF10000)

          Average:
          =SUBTOTAL(1,CF$7:CF10000)

          Min:
          =SUBTOTAL(5,CE$7:CE10001)

          Max:
          =SUBTOTAL(4,CF$7:CF10002)

          So this is a combination of using VBA and using Excels powerful SUBTOTAL functions.

          zeddy
          Excel Poets Society

          Ask-woody-anbuselvam-rz3

          Attachments:
          1 user thanked author for this post.
      • #1862318 Reply
        WSanbuselvam11
        AskWoody Lounger

        Dear Zeddy

        It is working good. Now onwards I will use auto filter codes in the VBA instead of writing long codes to get the desired results.

        Really I learned the new way to drill down the data.

        Thanks a lot for all your effort and time.

        I will contact you for the support in the future VBA code writing in the other requirements.

        Sincerely Yours

        Anbuselvam K

    Viewing 19 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Error in VBA code – Need solution

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.