I'm having problems getting David McRitchie's pushdown macro to work
correctly. I've worked through the code and changed some "<" to be
"", and vice-versa, achieving some minimal success doing this, however
I still cannot get the desired results. Can anyone tell me why this
code isn't working correctly???

The pushdown macro can be found at:
http://www.mvps.org/dmcritchie/excel/pushdown.htm

Thanks in advance!!!

How is it not working?

When I run it through exactly as it is posted online it ends up sorting
the data correctly, however the pushdown part of the macro does not
work correctly and instead it ends up only adding one blank row under
the title row. Seeing that it wasn't working properly I tried to
troubleshoot by changing:
UCase(Cells(r, c).Value) < UCase(val) Then
to:
UCase(Cells(r, c).Value) < UCase(val) Then
under the "Find lowest value in a row" section of the code. This
allowed val to be set to the correct value (or so I believe), so after
some more troubleshooting I changed a similar line in the "if not the
lowest value in row push down values in column" section in a similar
fashion. By doing this the macro will actually "push down" the data,
however I still cannot get it to work as it is said to work (???).

I truly believe this is something simple, but I just cannot get the
code that's posted to work correctly.

Why do you need to change it at all? What doesn't it do that you want?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
ups.com...
I'm having problems getting David McRitchie's pushdown macro to work
correctly. I've worked through the code and changed some "<" to be
"", and vice-versa, achieving some minimal success doing this, however
I still cannot get the desired results. Can anyone tell me why this
code isn't working correctly???

The pushdown macro can be found at:
http://www.mvps.org/dmcritchie/excel/pushdown.htm

Thanks in advance!!!

Sorry, I changed:
UCase(Cells(r, c).Value) < UCase(val) Then
to:
UCase(Cells(r, c).Value) UCase(val) Then
(just changed the "<" to "")

I only modified it in an attempt to get it to work correctly. I just
want the code to work as it says that it will work.
Thanks for everyone's help so far, & I'm trying to be as descriptive as
possible. The major error is that the code posted by David McRitchie
is not working for me.

It works just as advertised as far as I can see.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
ups.com...
I only modified it in an attempt to get it to work correctly. I just
want the code to work as it says that it will work.
Thanks for everyone's help so far, & I'm trying to be as descriptive as
possible. The major error is that the code posted by David McRitchie
is not working for me.

If I paste the pushdown code exactly as it is posted on his webpage in
a module, all his code does for me is sort each column of data and
insert a cell in Row 2 of each Column, leaving Row 2 as a blank row.
Absolutely no offense, but you're saying that you can do the same thing
and it works correctly for you??? What could be going wrong when I run
the macro to cause the previously mentioned results???

I stepped through it again. Essentially, after the code sets val =
too_high, the value for val never is reassigned to the lowest value in
the row. Due to this, when the code asks "If val = too_high", val
still does equal too_high, so the macro is prompted to finish. And the
cells are added in Row 2 because the code as written inserts a Cell if
(r,c).Value val, which in 99.9% of cases that will be true. Any help
I've previously received is greatly appreciated, however I still don't
see how the code as posted can work correctly (???).

I don't know Brian. What does your data look like?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
ups.com...
If I paste the pushdown code exactly as it is posted on his webpage in
a module, all his code does for me is sort each column of data and
insert a cell in Row 2 of each Column, leaving Row 2 as a blank row.
Absolutely no offense, but you're saying that you can do the same thing
and it works correctly for you??? What could be going wrong when I run
the macro to cause the previously mentioned results???

I'm using the sample data. After running the macro the results are as
follows:

Bh72 R.sol A.tum EbN1 N.euro G.metali

PF00013 PF00013 PF00013 PF00013 PF00013 PF00013
PF00015 PF00015 PF00015 PF00015 PF00015 PF00014
PF00023 PF00023 PF00023 PF00023 PF00016 PF00015
PF00027 PF00027 PF00024 PF00027 PF00023 PF00023
PF00028 PF00032 PF00027 PF00032 PF00027 PF00027
PF00032 PF00033 PF00032 PF00033 PF00032 PF00032
PF00033 PF00034 PF00033 PF00034 PF00033 PF00033
PF00034 PF00035 PF00034 PF00035 PF00034 PF00034
PF00035 PF00036 PF00035 PF00036 PF00035 PF00035

Well I have just tried the sample data and it worked fine.

It might be copying the data from the web that is a problem. Try this data
with Dave's original macro.

Bh72 R.sol A.tum EbN1 N.euro G.metali
PF00013 PF00013 PF00013 PF00013 PF00013 PF00013
PF00015 PF00015 PF00015 PF00015 PF00015 PF00014
PF00023 PF00023 PF00023 PF00023 PF00016 PF00015
PF00027 PF00027 PF00024 PF00027 PF00023 PF00023
PF00028 PF00032 PF00027 PF00032 PF00027 PF00027
PF00032 PF00033 PF00032 PF00033 PF00032 PF00032
PF00033 PF00034 PF00033 PF00034 PF00033 PF00033
PF00034 PF00035 PF00034 PF00035 PF00034 PF00034
PF00035 PF00036 PF00035 PF00036 PF00035 PF00035

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
oups.com...
I'm using the sample data. After running the macro the results are as
follows:

Bh72 R.sol A.tum EbN1 N.euro G.metali

PF00013 PF00013 PF00013 PF00013 PF00013 PF00013
PF00015 PF00015 PF00015 PF00015 PF00015 PF00014
PF00023 PF00023 PF00023 PF00023 PF00016 PF00015
PF00027 PF00027 PF00024 PF00027 PF00023 PF00023
PF00028 PF00032 PF00027 PF00032 PF00027 PF00027
PF00032 PF00033 PF00032 PF00033 PF00032 PF00032
PF00033 PF00034 PF00033 PF00034 PF00033 PF00033
PF00034 PF00035 PF00034 PF00035 PF00034 PF00034
PF00035 PF00036 PF00035 PF00036 PF00035 PF00035

Whoa! I had trouble correctly copying and pasting what you put into
Excel, so I just typed up some sample data and it ended up working. Do
you have any clue why it won't work with the data from the web, because
it unfortunately won't work with the data I'm trying to sort, either.
I'm wondering if I need to change some formatting or something of that
sort? THANKS A TON for at least bringing me some sanity on this,
however!

Figured it out. If there's a space or a "_" at the beginning of ANY of
the data it will not work correctly. I'm just going to have to write
something up to reformat the data before I run this.

THANKS FOR ALL OF YOUR HELP, IT REALLY IS APPRECIATED!

Lightbulb: I just changed the value of too_high to equal "___" instead
of "ZZZZZZZZ" and it works like a charm...

I am not all that experienced with VBA, but I tried the macro using his
data from the web page cited and it worked for me.

I then substituted a random list of fruit names such that not every row
had every name and was not sorted in any manner.

When I ran the macro, it sorted such that each row had the same fruit,
if it was in the column, otherwise there was a blank cell.

I think that is what the web page said it would do.

The only issue I had was that we are running Office 2000 on Windows
2000/NT and the

all_done:
xlong = ActiveSheet.UsedRange.Rows.Count _
+ ActiveSheet.UsedRange.Columns.Count 'Tip73

gave me a "Variable not defined" dialog until I dimmed the xlong as a
variable.


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=544373

Hi Brian,
interesting because Excel uses it's own collating sequence and the
underscores would sort before numbers and letters as long as the
cell is not a number. Anyway a problem with the original data from
the web is that you are not going to see all characters. I would suggest
that you copy from the web page, then use data, text to columns
separating on a space, tab and all that with two spaces same as one space.
Then the important part, run the TRIMALL macro as found in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I tested with the data you posted (copied from my page) and it
got almost the same results as you describe (except for first column).
Removing the blanks to start over, running the TrimALL macro and it
ran fine.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message oups.com...
Lightbulb: I just changed the value of too_high to equal "___" instead
of "ZZZZZZZZ" and it works like a charm...

That problem is caused because, as you say, xLong is not defined, and you
must have Option Explicit in your modules (as you should <G).

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"DCSwearingen"
wrote in message
news:DCSwearingen.2889vn_1148333106.6822@excelforu m-nospam.com...

I am not all that experienced with VBA, but I tried the macro using his
data from the web page cited and it worked for me.

I then substituted a random list of fruit names such that not every row
had every name and was not sorted in any manner.

When I ran the macro, it sorted such that each row had the same fruit,
if it was in the column, otherwise there was a blank cell.

I think that is what the web page said it would do.

The only issue I had was that we are running Office 2000 on Windows
2000/NT and the

all_done:
xlong = ActiveSheet.UsedRange.Rows.Count _
+ ActiveSheet.UsedRange.Columns.Count 'Tip73

gave me a "Variable not defined" dialog until I dimmed the xlong as a
variable.


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=544373

Hi Brian,
modification to macro in
http://www.mvps.org/dmcritchie/excel/pushdown.htm
sorry for not bothering to also create the code/pushdown.txt
but it is one less thing to worry about for updates and it is
essentially only one macro of interest and not likely to be
copied incorrectly once I've corrected <, , and < in the
HTML coding.

I wanted to keep the macro understandable but it would
be much better to use a string of High Values for testing
instead of "ZZZZZZZZZ" so I've changed the line to

too_high = application.REPT(CHR(255),20) 'instead of "ZZZZZZZZZZ"

Also for retesting there is a pushdown_restart macro specifically to
put data back to original test data positions, which I'd forgotten
was available.

Something else, besides running TrimALL on the data, that you have to
watch out for is copying coding from web pages:
< less than characters,
greater than characters,
< not equal as combination of the two
" double quotes (which shouldn't be a problem here so I've not changed)

I have corrected the HTML coding to use &lt;, &gt, and &lt;&gt;
internally, so that you can copy it correctly. There may also be differences
in different browsers if not provided for -- I did not check because it was
bad HTML coding anyway, but an HTML syntax
checker would probably have flagged bare < Excel operators.

Suggest you recopy from the web page because your changing of less
than and greater than signs and the characters being tested does not
sound correct.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" wrote in message
I tested with the data you posted (copied from my page) and it
got almost the same results as you describe (except for first column).
Removing the blanks to start over, running the TrimALL macro and it
ran fine.