Excel Macro or VBA IF/Then, Do While, Loop, Arrrgggh

Topic

New Reply

Hi all,

I am working on an Excel 2003 Workbook that has four sheets (attached is a simple sample with two). I have been trying to figure out the best way to accomplish the following:

I need each of the cells in sheet2 from B2:H32 to compare the date in column A and the name in row 1, to the sheet1 dates in colum B and names in column E.

The total for the number of rows each of the dates and names match should be reflected in the corresponding cell in sheet2.

First I played with a formula in each cell, then I played with a VBA macro module, now I just have a headache and another indication of just how little I know.

Over a year time frame, the number of rows in sheet1 will end up in the thousands, with from 30 to 60 or so entries for each date in the year. I would expect what is currently sheet2 will end up being 12 separate sheets, one for each month. I have never used Excel with that many records, and am concerned that as it gets larger it will just grind to a halt. Is this a valid concern? We would be using Access instead, except we do not have it available.

Any help with this would be greatly appreciated.

Reply | Quote

Replies

Hi

See attached file.
I added a cross-ref file in column [F] of your datasheet Sheet1.
On Sheet2, I used a formula to check against this cross-ref:
=COUNTIF(Sheet1!$F:$F,B$1&$A2)

It gives the answers you want.

zeddy

Reply | Quote

Zeddy answered the question, but as a comment to some other things you mention. I would never advise splitting worksheets into separate identically formatted sheets (like months). I woud always keep them together. If you want to view a month, you can filter (I often create a year column and month column so if desired I can filter on a particular month and year more directly).

Having one sheets allows the use of a filtering, subtotals and pivot tables.

The output desired can be done direclty, without a macro and without formulas with a simple pivot table (date as rows and names as columns, with a count of the names as the data field). It even allows grouping of dates and/or filtering on years, etc.

Steve

Reply | Quote

Hi Tropicalroo

Steve’s answer is better. That’s why he’s a Lounge VIP. Use a pivot table.

But if you are in Hawaii, perhaps you should be going to the beach on a Saturday.
And it’s the first time I answered someone from a US State that has a Union Jack still on it’s flag.
Actually, as you know, Hawaii is the ONLY US state that has this.

Steve – I like the horses on your flag.

zeddy

zeddy

Reply | Quote

Thank you both for the responses. I have tried both and they worked well. The Pivot Tables were the winner in the end. The formulas worked also, but as I feared, having to do so many of them slowed my fairly fast PC down a bit and would have choked the PC this will be running on.

I now have 2 PT’s with Date as rows and data, and with Dealer and Driver respectively as a column. Totals for each Dealer and Driver is working great. I have reformatted the date column in both the database and the PT’s to show as ‘ddd mm/dd/yyyy’, so we know at a glance what week day a specific date is.

Now, I have been working on a table that will do a count of the number of times each weekday has occurred to date (Mon’s, Tue’s, Wed’s, etc.). I do not see a way to get a Pivot Table to do this, though there probably is and I do not know enough to figure it out via Excel Help files, and have tried several different formulas in a separate table, including DCountA with ‘Mon’ as criteria, and have not found something that works correctly.

Once again, I appreciate any help on this you may be able to provide.

TR

Reply | Quote

… but as I feared, having to do so many of them slowed my fairly fast PC down a bit and would have choked the PC this will be running on…

Have you set “Calculation Mode” to Manual? If not, that will help prevent some slow-downs. Use the F9 key to force a recalculation when you want the sheet update.

Reply | Quote

I think the easiest way would be to create a “Day of the Week” column using the weekday function. It will give a number from 1 (Sun) to 7 (saturday). You can format that number as “ddd” to display the number as Sun – Sat or as “dddd” to display it as Sunday – Saturday. You can use this a pivot table field for summarizing and analyses

Steve

Reply | Quote

TR,

thanks for posting this question, I picked up a few pointers. Could you post your finished spread sheet?

Reply | Quote

Hi again,
I have attached two workbooks, one using mostly pivot tables (PT 1.5) as Steve suggested, and one using formulas (FM 1.1)as Zeddy suggested.

The one with pivot tables also has two small formula tables. This workbook is the one we are actually using, though for some reason the pivot tables messed up yesterday and again today for reasons as yet unknown. Yesterday when doing a data refresh on them, an error message popped up stating the tables were invalid and did not update. I deleted them and made new ones, and they worked until this morning, when they stopped working without any error message at all. The ones in the attached file work, but only have a few sample entries in them as I had to delete the 800+ real records to protect the guilty:^_^:.

The one with all formulas actually ended up not slowing the PC’s down as much as I thought, and with it the reports all fit on one page. The down side is that several parts will have to be changed each month, which is why I preferred in the end to work mostly on the one with pivot tables. Well, that and I can get the Call Record Ref columns O-Q to autocomplete when I directly enter information into the cells, but when the information is copied over from the “Call Sheet” via the “CopyClear” VBA macro, they do not.

Steve, you may recall this first started a while ago when you helped with answers on getting dynamic data from one sheet into a second sheet that would be static (Call Sheet and Call Record now). We have been using the result of that and it has worked extemely well. Please pardon the unsightly mess I have undoubtably made of the various code whilst tromping around in it

Anyway, it is still a work in progress, and I can only say yet again that I thoroughly appreciate everyone’s help. And I will probably be back again…

TR

Reply | Quote

Hi TR

I’ve looked again at your files.

When working with pivot tables, aside from using formulas within them, you can generally only get results with a pivot table based on what is actually in the pivot source range.
For example, if there are NO shuttle runs on a particular date, then that date will NEVER appear as a record row in a pivot report of say days-in-the-month-as-a-row with Driver-names-as-columns.
Of course, you can still summarise by entire month totals etc. Similary, if any of your Drivers was on holiday for a month or more, you might not see any column data for them for particular months.
With a pre-defined report layout, you can ensure some consistency.

So there is a trade-off between setting up a specific report layout, and using formulas to ‘fetch’ and ‘process’ the data, and using the simplicity of data analysis using pivots.
Often, I use a combination of both i.e. define a report layout and use formulas to fetch data from pivot tables as a source of data.

So I had another look at your file FM 1.1xls and have attached a modified copy for you to look at, purely for ‘training’ purposes.
First of all, I tried to work out what was making your file at 480KB so big.
It wasn’t the formulas, it was your embedded graphics. I took them out and replaced them with the much small ‘gif’ based images. My attached file is 140KB.
In my attached file, my buttons are in fact a button object with a text box overlaid on top.

I simplified the formulas.
In cell [C3] I used
=COUNTIF(colN,B$2&$A3)
where colN is a range name rather than the whole column.
To stop the display (and printing) of any zeros, I just changed the cell format to a custom format of
0;;
In column [K], I added some formulas to fetch the weekday number of the date in column [A], and used a custom format of
;;;
..which makes these formulas invisible and non-printing.

I also added a month and year drop-down in cells [L1] and [M1] on sheet [Call Report]
Selecting a month using these dropdowns automatically populates the dates in column [A], always starting on the first day of the month.
I used conditional formatting to make Sundays red as before.
I changed a few other formulas as well, for you to check out.

Also, I was never keen to have VBA subs with the same name but in different modules.
So I took the liberty of changing these by way of example.
When you click the [Copy/Clear] button on the [Call Sheet], the VBA now takes care of the formulas in cols [N:Q] on the [Call Record] sheet.

If you have any questions, just ask.

zeddy

Reply | Quote

Hi Zeddy, and thank you for the response. It is way “above and beyond”, and exceptionally cool from my standpoint

I have been looking at the various code and formula changes, and believe I am making at least a little headway in understanding what you are doing. As I get to take more time to follow it, things should get clearer for me. For now, a couple of things I am unsure of:

1. On the Call Record, cells N1:Q1 are a mystery to me. They have the same formulas as the cells below them beginning in row 3, but reference the top row areas under the buttons used for sorting, protecting, etc. Additionally, where N1 and O1 show a result of “Shuttle Service Call Record” (the title in that row), P1 results in “0”, and Q1 results in a “#VALUE” error. I assume the formulas are actually not needed there, but not sure.

2. The Advisor portion on the report sheet does not clear when the month or year are changed, which may be due to the above referenced cell O1 showing an error?

I hope to be able to spend more time going over it all tomorrow evening, and make better progress in seeing what you have done. I can say even now, that I like where it is going.

Crash time for me now…

TR

Reply | Quote

Hi TR

To answer your questions:
1. On the sheet [Call Record], the formulas in [N1:Q1] are indeed ‘same as formulas’ in cells below.
This is just a way of keeping a ‘copy’ of the required formulas used in the cells below.
Ignore the results of these formulas in row1.
We can use these ‘pre-defined’ formulas at any time to ‘refresh’ the cells below, by copying and pasting these formulas.
If you were to paste back all your records you deleted from row11 downwards, you can copy the formulas for these manually added records.
I have added a button with some simple VBA code to do this in the attached file.
Also, this button will help you ignore the formulas underneath.
I amended the cross-ref formulas to now include both the year and the month in the cross-ref, rather than just a month number (in case your records had date values in more than one year)

2. The Advisor portion didn’t clear when the month changed because the previous formula gave a total for ALL records, not by month and year.
The new formula now gives the Runs by Advisor for the selected month and year.
(The cross-ref formula in column [P] on sheet [Call Record] was amended to include both month and year in the cross ref).

New bits in attached file:
Looking at your original file, your formulas in column [J] for the weekly totals were ‘fixed’ for specific rows that were Sundays.
In the new attached file, since the rows for a Sunday may now change depending on the month selected, a new set of formulas have been put in place to deal with this.
I have also used ‘conditional formatting’ to highlight any row that is a Sunday.

On the [Call Record] sheet, I inserted an empty row2 (and then hid row2) to make sure the records together with their record headers are in a ‘single’ block selectable as a ‘current region’.
This is to simplify any sorting used within the record block. I amended the sort routines accordingly.

In terms of other bits, you should give some thought to your vehicle mileage section.
This does not currently include any data for say, Buick, Jeep etc.
Also, perhaps it should use formulas to ‘retrieve’ this mileage data from another (new) record sheet where monthly mileage records could be updated.

And what goes in ‘Drop Off’ and ‘Pick Up’?? Is it a time? Is it pizza? Body parts? Just curious.

zeddy

Reply | Quote

Zeddy, I am quite simply at a loss for words at this point. What you have done with this is, to me, amazing. I have been going through the workbook as time allows, and have managed look at most of what you have done, including your comments. I understand a fair amount of it, at least in general, and for now will just consider the rest to be magic. It definitely does what we need it to do (and more), and does it in a much cleaner way throughout.

Your comment regarding the vehicle mileage definitely hit a mark, and it has been added to the “to do” list. Though we cannot narrow it down to miles per dealership, doing a monthly chart pulled from another record sheet as you suggest, containing each vehicles ongoing daily mileage will provide a better break down and record.

As for the Drop Off, Pick Up, and ETR boxes, what goes in them is 4 numbers based on the 24-hour clock, formated to display as a time (e.g. 13:30). Since their main use currently is for them to be on the Call Sheet when it is printed, and not much else, I went with just formatting them to look like a time. I did, briefly, look at actual time entries, but all I found required entering the colon in the middle or AM/PM on the end, so we opted for quick over correct for now. Another line on the future list.

Since I have made a couple of cell location and reference changes on the Call Sheet as an additional visual aid for the drivers, I have attached a copy of the updated version. Now, I get to go spend some quiet time and go through what you have done some more.

Thank you once again. All of your help with this continues to be greatly appreciated.

TR

Reply | Quote

Hi, it’s me again…

Everything is working perfectly in the program, and it has made a huge difference in information tracking.

Now, based on your (Zeddy) last comment, and a good one, there is one last hurdle:

“In terms of other bits, you should give some thought to your vehicle mileage section.
This does not currently include any data for say, Buick, Jeep etc.
Also, perhaps it should use formulas to ‘retrieve’ this mileage data from another (new) record sheet where monthly mileage records could be updated.”

That said, I have set up a second record page “Van Record”, added another table at the bottom of the Call Report page named “Shuttle Vans”, and added a section to the end of the VBA macro “CopyClear”. At this point it just duplicates what you have the reports above it do in counting entries, only it pulls from the Van Record page. (File attached)

I have tried to sort out what a formula in the van report cells might look like that would check van and date in Van Record, then take the mileage from that van and date, subtract the mileage from the row above it, placing the result in the cell in the report table. Based on what I think I was getting an understanding of from your formulas, I have tried some variations using IF statements, but know I continue to miss the boat right from the start since the record pages are ongoing lists, not static cells, and I guess I am just a static kind of guy.

There are reasons why I prefer just driving one of the vans!

Help, Again, Please?

TR

Reply | Quote

Hi
See attached file.

We can fetch the mileage data using simple VLookup formulas.
This assumes that the mileage records are appended in date sequential i.e. sorted order, from top to bottom, earliest date first.
So, for your sheet [Van Record], you don’t need any cross-ref formulas, so I removed all of them.

On sheet [Call Report], the starting mileage in cells [M3:M6] uses Vlookup formulas to ‘find’ the first day of the selected month (i.e. the date that is always in cell [A3] ) in the [Van Record] data block.

The [Van Record] data block I have defined as [A3:E403] : i.e. allowing ~400 record rows i.e. approx 1 per day for just over a year to allow for some spares running into more than a single year.
If we wanted to have three years of daily mileage records we could just change this range in the formula cells to [A3:A1103] (since 1100 rows is more than needed for 3 yrs etc).
If we want to be really clever, we could assign a dynamic range name to the [Van Record] mileage data block which would automatically adjust the lookup range as mileage reords were appended.

Chrysler is in lookup column 5, Honda is lookup column 4 etc. Check the formulas in [M3:N6]
We could put a formula in to actually match and find exactly which column the vehicle make is in, but as we already know it, why bother to make it more complicated.

The ending mileage in cells [N3:N6] on sheet [Call Report] uses the Vlookup formula to ‘find’ the end-of-month date.
What is the end-of-month date? I put a simple formula in cell [N1] to find it (clue: it’s either the 29th, 30th or 31st) and use this cell in the formula.
What if the end-of month date hasn’t arrived yet? Well, the way Vlookup works with sorted (i.e. in our case, date numerical order) date is that if it can’t find an exact match it uses the last record found.
So in the example file, if you click the month to a future month, you’ll get the last mileage readings for both the start and end dates and so zero miles used.
If you click the month dropdown and select Jan, you’ll see a stack of #N/As : reason, your mileage dates don’t start until 1st Feb 2012.
You can ‘fix’ this by putting in relevant data for January.

Important note: You must only have one record for any given day, covering ALL vehicles i.e. can’t have 2 records with same date (otherwise Vlookup won’t know which to use)
In cell [N1] you can change the font colour to ‘hide’ the end-of-month date showing: pick font colour same as background cell colour.

Hope this helps! Happy driving.

zeddy

Reply | Quote