Macro to Pick Out Date of Negative and Max Negative Values by Item for Summary

  • Thank you JonathanVH. I'm still trying to reconcile what the difference is. It's now telling me "No value given for one or more required parameters. I'm checking the column headers now.

  • I think we've got it! I had some a couple of spaces in 2 of the headers i couldn't see. I trimmed all the headers and it worked!!!!!
    Thank you so much!!!!!

  • If the headers are created by the "data dump," then change the VBA (SQL) code rather than trimming the values each time the data comes over. E.g., If a heading has "Branch " (with a space as the last character), then change the SQL code to match that:

    sql = "SELECT [Branch ], [Item Number], MIN([Start Date]) As [Date Negative], '' As [Month], MIN([Available Quantity]) As [Max Qty Negative] " & _
            "FROM [Sheet1$] " & _
            "WHERE [Available Quantity] < 0 " & _
            "GROUP BY [Branch ], MONTH([Start Date]), [Item Number];"
  • Absolutely! It works like a champ! I do have one last question though, i'm currently having to save the macro into the "data dump" workbook to run it. Can the code be easily modified to run from my PERSONAL.XSLB? It's where i store almost all my macros. The "data dump" overwrites each day.

  • I just used that as a proof of concept. Where do you want your results? If you want then on the same sheet as the data, but in columns to the right, then you could use something like:

    With ActiveSheet.Range("M1")

    If you want them on a the second sheet (and there is a second sheet in the workbook), you could change that to something like this:

    With ActiveWorkbook.Worksheets(2).Range("M1")

    This all assumes (as does the former use of ActiveWorkbook) that the workbook with the data is active when the macro is run.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!