Replies

Hans, thanks again for your on-the-money detailed explanation.

Right after I posted my last message I found CLng in VBA help and did some reading on it, so between that and your explanation, I think I have it. I’ve found this area of VBA to be difficult to penetrate; there doesn’t seem to be much in the way of help on these issues, so it looks like a matter of just plugging away until you figure it out (or, as in this case, until someone shows you).

I really appreciate not only your coding suggestion but also your very clear explanations of these things.

Regards,

Hans,

Thank you for your solution. It works perfectly.

But perhaps you can explain the CLng(ReportEndDate) structure to me. What is CLng?

Also, what would be the best way to format these numbers as dates?

Thanks again,

In the past, I have encountered users who have made similar claims. The unfortunate thing is that this type of problem is really hard to nail down. I think the first thing to determine is if this behavior occurs whenever Excel is running or does it occur only when a certain file is open. If it’s a specific file, that file is probably corrupt. If it’s not a particular file, it could be a faulty Excel installation. It could also be an add-in issue if the user has something like FactSet or some other outside service.

You could reinstall MS Office to see if that alleviates the problem. If not, I think the best way is to start narrowing the symptoms down. Have the user keep a notebook so he/she can jot down what was open and what happened if the system crashes again. See if the system crashes when just Word or Outlook is open.

I’d be interested in hearing the solution if you do arrive at one. Good luck…

Hi John,

Thanks for your suggestion. I am inferring that, based on your suggested approach, that the selected file(s) would have to be open (i.e., there is no way to get the info without opening the file). I like your idea about using a combo box to let the user select the file from a combo box. I’ll give it a shot and let you know how it comes out.

Thanks again,

Hi John,

Forgive me for not responding sooner, but the weekend got in the way

Anyway, your suggestion did work. And the user is happy, despite the fact that neither of us knows why the original construct doesn’t work.

Thanks again for your efforts and your suggestions. I really appreciate it.

Regards,

Actually, that doesn’t work either. I selected cell G132, which contains a hard-coded “2”, and the results in the table calculated correctly. Then, when I changed the contents of G132 to “=D87”, the table again calculated incorrectly.

I just can’t think of a reason why this occurs. It’s pretty weird. Perhaps I should query Microsoft directly…

Thank you for your response, though. If I come up with an answer, I will be sure to let you know what I find.

Regards,

Well, your response is exactly why I described my comments as “editorial”. I certainly didn’t want to sound dogmatic and I figured if my limited and singular use of Excel did not reveal to me why users might want an approximate match, then someone would point such examples out to me.

So I thank you for your instructive answer.

Just a quick comment on your response:

You are correct that the first column must be sorted in ascending order for VLOOKUP to work, but ONLY if the fourth argument is TRUE. If you sent the fourth argument to FALSE, the lookup range need not be in ascending order.

Editorial comment here: in the dozen or so years I have worked with Excel in banking environments, I have never encountered a situation where the fourth argument needs to be set to TRUE. When the fourth argument is set to TRUE, the lookup range (as mentioned above) does need to be in ascending order, AND Excel will not return the #N/A error if a match isn’t found. Instead, Excel returns the nearest lesser value in the lookup range. In my experience, users always want either the #N/A error (which indicates the lookup item is not in the lookup range), or an exact value from the lookup range based on the lookup item.

Hope I wasn’t too unclear…

Jebby,

If you mean you can’t even grab the page break line with your mouse, try this:
Tools–> Options–>Edit tab–>Enable Cell Drag and Drop.

If Drag and Drop are not enabled, you cannot move the page break lines in Page Break Preview.

Hope this helps,

Another thought is this: instead of mailing your working file to your customers, do a File Save As, give the new file a different name (possibly the customer’s name, perhaps impressing the customer), convert the formulas to values in the new version and then delete the two columns of confidential data. Wouldn’t take much time at all, avoids macros and links, and protects your data.

Good luck,

Hi John,

Thanks very much for your reply. I think your suggestion about creating a .BAS file and editing in that mode is really creative and I’m gonna try it first thing next week. Out of curiosity, how did you learn about deleted code “sticking around” in a module? I would never have thought to even ask that question.

Thanks again and best regards,

That’s an interesting experience. I have a problem wherein I have several very large (14 – 20MB) Excel files that contain pivot tables (up to eight in a file, each on its own worksheet). We use a lot of code to reformat, update and refresh these tables, and each table has its own cache (we tried sharing caches, but that didn’t work for us). There are no blank modules, but there probably is some code and/or some entire macros that could be eliminated. Would you have any idea if that might affect file size as well?

Also, each file has three modules: one for code that prints the tables, one for code that updates the tables and one for code that modifies (extensively) the database. Do you think putting all the code into one module would reduce file size?

Thanks in advance.

Yes, refreshing a pivot table can be downright frustrating after you’ve spent some time formatting it. The only way to insure you get your formatting the way you want is to create a macro that formats it after a refresh.

I do a lot of work with pivot tables, and I can tell you from experience that if you do go the macro route, you should copy the code to your worksheet (in the VBA editor) to use as a CHANGE event-driven macro. That way, if you (or anyone) uses the pivot table to select different options, the event code will immediately run and reformat the table.

Here is some code that I use in one of my tables to format every other row in gray shading.

1. Range(“A3”).Select
2. Range(Selection, Selection.End(xlToRight)).Select
3. Selection.Offset(1, 0).Select
4. Range(Selection, Selection.End(xlDown)).Select
5. Selection.FormatConditions.Delete
6. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= “=MOD(ROW(),2)=0”
7. Selection.FormatConditions(1).Interior.ColorIndex = 15
8. Range(“A1”).Select

Here’s what it does:

Line 1 selects cell A3, which is where my pivot table begins.
Line 2 selects all the populated cells in row 3. (This row contains the table’s column headers.)
Line 3 changes the selection to row 4, as I don’t want to reformat the column headings.
Line 4 selects all the populated cells in the columns (completing the selection of the pivot table).
Line 5 deletes any current conditional formatting within the table.
Line 6 applies the formula that selects every other row in the selection.
Line 7 applies the light gray shading to the selected alternate rows.
Line 8 leaves A1 as the active cell.

Hope this helps.

Regards,

Please feel free to call on me anytime. I’ve been on this pivot table project since last November and I’ve learned A LOT about pivot tables and how they work.

Good luck…

What I did was name the pivot tables when I created them. You can do this by right-clicking anywhere within the pivot table, then selecting Table Option. The very first item in this dialog is the name. Just type in the name that you want and you can use that name in your macros. (If you don’t name your table, Excel simply gives it a generic name (PivotTable1, PivotTable2, and so on), and that can get very confusing if you deal with a lot of pivot tables (like I do).

FWIW, I have several Excel files that my users use as their pivot table templates. The files already contain various pivot tables based on sample data. That way all my macros have to do is update the existing tables with the new data (although that in itself has become quite complex, for a lot of different reasons).

When the file is opened, there is a blank worksheet named “Paste New Data Here”, and the user then pastes the new data into that sheet. Then the user selects the particular pivot table needed and clicks a macro button (each pivot table has its own macro button) that begins a series of complex actions, including copying the new data to a different worksheet (which I named “Pivot Data Base”), modifying it (adds columns and formulas) and naming it for further use with the macros. Each pivot table uses its own cache and has its own update macros, all of which call the macro that copies and modifies the database.

When the update macros complete execution, the pivot table is updated and formatted in accordance with the specifications they use here. Everything is automated so the user doesn’t have to do any actual work with the pivot table itself.

Regards,