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

    This topic contains 25 replies, has 7 voices, and was last updated by  WSanbuselvam11 2 months, 2 weeks ago.

    • 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

      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 2 months, 3 weeks 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 2 months, 3 weeks 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

      I have not failed. I've just found 10,000 ways that won't work.
      --- Thomas A. Edison

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

      anonymous

      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

      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

        I have not failed. I've just found 10,000 ways that won't work.
        --- Thomas A. Edison

        • This reply was modified 2 months, 2 weeks 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 2 months, 2 weeks ago by  zeddy.
      2 users thanked author for this post.
    • #1861034 Reply

      anonymous

      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

    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.