Posts by JonathanVH

    Okay, you made me download his file. I see that he is looking in column B for the blanks (note the name of my above Sub). I also noticed that he had a typo in the fourth range address.

    But using hard-coded addresses for what are obviously dynamic ranges is not a realistic methodology. Again, consider using tables (ListObjects in VBA) for these orders because then the code can adapt for the number and sizes of orders. Better yet, have just one Excel order range for doing the formatting and fill that from orders in a database.

    You never mentioned anything about cell colors. I thought you had recorded a macro to delete the relevant cells. So you now want to clear the contents (but not the formatting?) of cells that are a particular color? That cannot be done with the macro recorder. Your code is missing the For statement to iterate through the cells on each sheet.

    So I thought about this, and I would still use SQL. ;) This groups by Branch, Item Number, and Month, and does not report any items that do not go negative.

    This is hard-coded that the data is at the top left of Sheet1 and the summary report is created on Sheet2.

    As it uses the Text property of the (cell) ranges, it will delete any row that looks as though it has a blank in the first column. Actually, I think I'd add a Trim to that, so change that row to:

    If Trim$(.Cells(r, 1).Text) = vbNullString Then .Cells(r, 1).EntireRow.Delete

    in case those cells contain spaces and still look blank.

    You're not using the right tools for the job. Sure, use Excel to format and display your data, but use a database, not Excel, to store your data.

    Rather than the interesting code above, I suggest something less abstruse.

    Cell A11 has a List validation using range A3:A7.
    Cell A12 has this formula: =VLOOKUP($A$11,$A$3:$B$7,2,FALSE)
    Cell B11 has: =INDEX($A$3:$A$7,MATCH(B$12,$B$3:$B$7,0))
    Cell C11 has: =INDEX($A$3:$A$7,MATCH(C$12,$B$3:$B$7,0)) so it can just be copied from B11
    Cell B12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0)) entered as an array formula so it displays as {=INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0))}
    Cell C12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),3),ABS($B$3:$B$7-$A$12),0)) entered as an array formula
    Cell A14, used as the chart title, has: ="Cities Closest in Population to "&$A$11
    The chart uses range A11:C12. To extend the chart to show more cities, copy C11:C12 to the right and then edit the copied formulas in row 12 to increment the number used with the SMALL function. Save as arrays after editing.


    That error sounds as though you attempted to edit what the macro recorder created. The macro recorder does not "record keystrokes." It converts any relevant actions you do and creates VBA code to get to the same result. E.g., if you click on a cell, it does not record your moving the mouse and clicking; it instead writes the VBA code for that action: something like "Range("C5").Select."

    Macros and Visual Basic for Applications (VBA) are two ways of saying the same thing. Using the Macro Recorder writes a program in VBA. The VBA code generated by the Macro Recorder is inelegant but it should work for this. You can then use Design Mode to add a button to a sheet and assign your generated macro to that button.

    Well, if the ranges can contain actual empty cells, then you will need to either explicitly define each separate range (i.e., the code will not dynamically adjust) or have some guaranteed way to identify header rows (and be okay with deleting all blank rows between rows.

    I just opened your file... Why not just use tables for those ranges? Then it would be simple to iterate through the sheet's Tables collection (and it would also be easier to hide or delete blank rows).