Complicated Macros Involving Filtering Columns

[marianumc]

Hello there -

Well just to make things really complicated - I am not super familiar with Macros. I began tutoring myself yesterday, and I am now somewhat familiar with Loops, Ranges, Cells, and programming language generalities, but I do not know any specifics regarding language definitions (what words to use, when to avoid caps, etc.)

So here is what I'm trying to do. I have a spreadsheet detailing items that we are expecting on a construction site. My co-worker would like me to be able to filter these items by date.

The column headings involve Description, Equipment ID, Spec Section, etc (Row 9). The first column (1) has the description of these items. However, there are headings that appear every so often under Column 1. For example, I have "Air Handling Units" in cell 12A, and under that heading in cells 13A-25 I have the different Air Handling Unit Titles (Cluster S1, S2), etc. I've attached a screen shot so you can understand what I'm referring to.

So here is what I've been assigned to do, please don't laugh:

-filter the "expected on site" column by date so newest dates show up on top.
-when items falling under different categories inevitably get mixed together because the dates will align them in that way, I'm supposed to trigger a function which will repeat the headings each time an item from its category appears. For the purposes of demonstration, lets say that Cluster S2 under Air Handling Units was first, then 128" x 48" (Quantity 24) under Sound Attenuators, then Cluster N1 under Air Handling Units. So essentially I'm trying to get it to look like:

Air Handling Units
Cluster S2
Sound Attenuators
128" x 48" (Quantity 24)
Air Handling Units
Cluster N1

With their respective columns (Equip. ID, Expected on Site).

It is okay to not worry about major headings "HVAC." So long as I could figure out a way to do this complicated filter, I would be happy. I have already decided that making each of the groupings in the first column should be their own list,and made them so to keep linkage between that info and their headings. However I stop at the Macros. Any ideas? I ask you not to make fun of me. I am a quick learner and would appreciate any honest help.

[shg]

Welcome to the forum.

The first step is to arrange the data in database fashion. Each record (row) should completely identify the item -- so get rid of the interspersed category rows in favor of a new column, and copy the category to each relevant row.

Then post a worksheet instead of a picture.

[marianumc]

Hi there -

Thanks for helping me out. I have made a new table that is not confidential that I have attached. Right now I have tried to simply record a Macro where I first choose a custom AutoFilter and set the parameters to greater than or equal to 02/23/2009 and less than or equal to 03/09/2009. Then I stop recording the Macro. It looks like this:

Please Login or Register  to view this content.

I then go in and have tried the following:

-subbing "TODAY()" in place of 02/23/2009 and "TODAY+14" in place of 03/09/2009
-subbing "Date" and "Date+14" in place of those same examples I just used
-placing "TODAY()" formula in a different cell and using it as a frame of reference.

I don't know what else to try to make this happen. If we can just first start with programming the Macro to pop up the next two weeks of delivery dates, that would be a great start.

[marianumc]

"Bump No Response"

[marianumc]

"Bump No Response"

[StephenR]

Perhaps somebody can explain why this doesn't work. When run it shows nothing but if I go into the filters via the worksheet the correct values are there and after pressing OK the correct rows miraculously appear!

Please Login or Register  to view this content.

[marianumc]

Thank you so much for trying to help! Hopefully someone will help us get this sorted!

[marianumc]

"bump no response"

[StephenR]

OK, this works for me. Just a simple date formatting issue. Like those in real life, dates in Excel can be rather fraught.

Please Login or Register  to view this content.

[marianumc]

This is GREAT! Sorry it took me so long to get back to you but I practically gave up on this. Can you just briefly explain why the macro has to be formatted as

& Format(Date, "m/d/yyyy")

I understand that you are telling excel this is the date's formatting, and that we are looking ahead based on today's date, but I just don't understand why the word Format appears to enclose Date, and the formatting. I would feel like it should have read Date (Format, "m/d/yyyy")

Also, I have one final thing that is bugging me. I now have a Macro that can pull up the upcoming two weeks in ascending order, and display only those items that haven't arrived yet (i.e. no date in the final column). However, since I'm pulling the information from another worksheet, the empty dates in the final column appear as "1/0/1900." Any easy way to get them to appear as blank cells (corresponding with their other worksheet counterparts?)

I can again attach the spreadsheet if needed.

[marianumc]

bump no response

[VBA Noob]

& Format(Date, "m/d/yyyy")

This line is saying format today's date as "m/d/yyyy"

empty dates in the final column appear as "1/0/1900."

Maybe a custom format. Try something like

[<=1]"";[>1]dd/mm/yyyy

VBA Noob