The Excel Questions Thread

[snuffy]

Wow! Great stuff.

How did you guys learn so much about Excel?

 

[dsgNYC]

Originally Posted by snuffy

Wow! Great stuff.

How did you guys learn so much about Excel?

slave to the man...and i learned a lot from this thread

 

[djs488]

I have written a macro that, among other things, creates a number of charts and formats them specifically. When I step through the macro in debug mode through the problem area, everything works properly. However, when I actually run the macro the graphs are not formatted correctly. Ever seen macros behave differently while debugging than when run? Anyone know a solution? Using excel '03 btw.

 

[dsgNYC]

Originally Posted by djs488

I have written a macro that, among other things, creates a number of charts and formats them specifically. When I step through the macro in debug mode through the problem area, everything works properly. However, when I actually run the macro the graphs are not formatted correctly. Ever seen macros behave differently while debugging than when run? Anyone know a solution? Using excel '03 btw.

I've had macros act up when I ran them once, then run fine when I tried again, but I've never had them run differently stepping through threm versus just running them. Do you have code you can share?

 

[djs488]

The code that is behaving differently when stepped through is pretty basic:

With ActiveChart.PlotArea
.Top = 1
.Left = 6
.Height = 127
.Width = 180
End With

It is simply supposed to enlarge the plot area in a graph from it's default size. When stepped through, it works properly. When the macro is run, the plot area actually appears much, much smaller than the default size.

 

[CunningSmeagol]

What happens when you comment out that code?

 

[rajesh06]

Originally Posted by djs488

I have written a macro that, among other things, creates a number of charts and formats them specifically. When I step through the macro in debug mode through the problem area, everything works properly. However, when I actually run the macro the graphs are not formatted correctly. Ever seen macros behave differently while debugging than when run? Anyone know a solution? Using excel '03 btw.
I have the same problem with a macro that doesn't set the print quality (600 dpi) correctly on a chart. My guess is that the chart is not properly activated when you run the macro - but I am guessing that you already thought of this.

 

[djs488]

Originally Posted by CunningSmeagol

What happens when you comment out that code?

Then the plot area remains it's default size; that is, the same size it was when the chart was created.

 

[dsgNYC]

Originally Posted by djs488

Then the plot area remains it's default size; that is, the same size it was when the chart was created.

When I use that code and step through it, my plot area becomes very small and is located in the top-left corner of the chart area. Are you sure there isn't another part of your macro that, when run (not stepped through), changes these numbers?

 

[CunningSmeagol]

When I step through it and run it, everything works properly. Scatter plot and pie chart.

Here's what I think could be happening. You're stepping through the code but not completing it. When you run it, however, you encounter code after the code you showed that positions the legend in the corner. When you do this, the plotarea shrinks. This is a known issue in 2002, but I was able to replicate it in 2003.

Try putting the plotarea code at the end of your procedure.

Edit: Here's the 2002 issue

http://support.microsoft.com/default...b;en-us;211606

Edit2: Seems to only happen with pie charts. Still, reformatting the legend after formatting the plot area could be causing the problem and wouldn't show up in your debug of the problem area. Even if it's something else causing the shrink, you can still correct the size issue by moving the plotarea adjustment to the end.

 

[unjung]

I have two lists of names. I need a quick way to determine which names are listed in both, and which are in only one. They could be flagged somehow, or could just be lined up next to their match. How to do?

 

[CunningSmeagol]

Originally Posted by unjung

I have two lists of names. I need a quick way to determine which names are listed in both, and which are in only one. They could be flagged somehow, or could just be lined up next to their match. How to do?

If your lists are in A1:A10 and B1:B10, put the following in C10 and drag down:

=NOT(ISERROR(MATCH(B1,$A$1:$A$10,0)))

It will return TRUE if the name referenced in B1 is found within A1:A10. And so on for B2, B3, etc. It will otherwise return FALSE. From there you could do all sorts of things like conditional format to make them red or hwatnot.

 

[unjung]

Originally Posted by CunningSmeagol

If your lists are in A1:A10 and B1:B10, put the following in C10 and drag down: =NOT(ISERROR(MATCH(B1,$A$1:$A$10,0))) It will return TRUE if the name referenced in B1 is found within A1:A10. And so on for B2, B3, etc. It will otherwise return FALSE. From there you could do all sorts of things like conditional format to make them red or hwatnot.
Thanks, that would have been perfect, but I realized that because there are duplicates of last names with different first names in different entries, it won't really work (some names also have different versions of first names, e.g. "Jim Wilfred Brown" versus just "Jim Brown")... gonna have to do a manual check I think.

 

[CunningSmeagol]

Originally Posted by unjung

Thanks, that would have been perfect, but I realized that because there are duplicates of last names with different first names in different entries, it won't really work (some names also have different versions of first names, e.g. "Jim Wilfred Brown" versus just "Jim Brown")... gonna have to do a manual check I think.

It can be done. How many names do you have to check?

 

[unjung]

Originally Posted by CunningSmeagol

It can be done. How many names do you have to check?

List A is about 450, List B is about 300. Technically there are two lists for each, first and last name. As I said, there are some include two names or errors which makes it a PITA. But it could be nice to get sorted out as I have two more lists of the same size to do afterward.