In a macro I have

;;; Lines
;;; not shown..
Range("A5").Select
Selection.Sort Ket1:=Range("C5"), Order1:=xlDescending, Header:=xlguess, _
OrderCustom:=1, Matchcase:=False, Oreintation:=xlToptoBottom
End Sub

Prior to the lines shown above my spreadsheet shows
A B C D
5 Bank LocNumber DepAmt DepDate << my Headers
6 WC 0000000000 44.00 12/01/05
7 WC 0000000000 38.00 12/01/05

After the above Code runs it is reflecting:

A B C D
5 Bank LocNumber DepAmt DepDate << my Headers
6 WC 0000000000 44.00 12/01/05
7 WC 9541211111 600,000.00 12/06/05
8 WC 9541211111 598,000.00 12/21/05

and so on down to $1.00

Why is it NOT CHANGING/CONSIDERING ROW 6?

TIA,

Jim

Jim,

I put the formula (changing a couple of typos) into a workbook and it
works perfectly. I'm as stumped as you are.

Are any of your numbers actually stored as Text?

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
...
In a macro I have

;;; Lines
;;; not shown..
Range("A5").Select
Selection.Sort Ket1:=Range("C5"), Order1:=xlDescending, Header:=xlguess, _
OrderCustom:=1, Matchcase:=False, Oreintation:=xlToptoBottom
End Sub

Prior to the lines shown above my spreadsheet shows
A B C
D
5 Bank LocNumber DepAmt DepDate << my
Headers
6 WC 0000000000 44.00 12/01/05
7 WC 0000000000 38.00 12/01/05

After the above Code runs it is reflecting:

A B C
D
5 Bank LocNumber DepAmt DepDate << my
Headers
6 WC 0000000000 44.00 12/01/05
7 WC 9541211111 600,000.00 12/06/05
8 WC 9541211111 598,000.00 12/21/05

and so on down to $1.00

Why is it NOT CHANGING/CONSIDERING ROW 6?

TIA,

Jim

Jim,

The only ways I can get the macro to fail is to either enter the number
in C6 with a leading apostrophe ('44.00), which makes it a text entry,
or to enter it as 44,00 (using a comma instead of a decimal point).

If the values in column C are forced right-justified, that would make
this kind of problem harder to spot.

I hope this helps. If not, I sure would like to know what's happening.

Thanks to ALL (you guys) for your input;
It's on my PC at work -- I'm now at home.
I'll get into it tommorow WITH your thoughts/comments.
with appreciation...
Jim

"Mark Lincoln" wrote in message
oups.com...
Jim,

The only ways I can get the macro to fail is to either enter the number
in C6 with a leading apostrophe ('44.00), which makes it a text entry,
or to enter it as 44,00 (using a comma instead of a decimal point).

If the values in column C are forced right-justified, that would make
this kind of problem harder to spot.

I hope this helps. If not, I sure would like to know what's happening.

Thanks Tom;
One line BEFORE the line of code I showed previously is
a Paste-Special Values statement, which "converted"
my LocNumber (Cell B6) from its Source Value of 0000000000 - formated as Text
and dropped it in as '0000000000 in the Destination Cell B6

Currently, I'm adding two lines Prior to the Range("A5").select as follows:

Range("B6").Numberformat = "@"
Range("B6").FormulaR1C1 = "0000000000" <<< not happy with this line
But source
info will "Always"
Begin with
"0000000000" if there are any

Would prefer somehow just removing the " ' " being programmatically inserted
and maintaining the text format from the source..

Any suggestions?

with appreciation..
Jim May




"Tom Ogilvy" wrote:

Are any of your numbers actually stored as Text?

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
...
In a macro I have

;;; Lines
;;; not shown..
Range("A5").Select
Selection.Sort Ket1:=Range("C5"), Order1:=xlDescending, Header:=xlguess, _
OrderCustom:=1, Matchcase:=False, Oreintation:=xlToptoBottom
End Sub

Prior to the lines shown above my spreadsheet shows
A B C
D
5 Bank LocNumber DepAmt DepDate << my
Headers
6 WC 0000000000 44.00 12/01/05
7 WC 0000000000 38.00 12/01/05

After the above Code runs it is reflecting:

A B C
D
5 Bank LocNumber DepAmt DepDate << my
Headers
6 WC 0000000000 44.00 12/01/05
7 WC 9541211111 600,000.00 12/06/05
8 WC 9541211111 598,000.00 12/21/05

and so on down to $1.00

Why is it NOT CHANGING/CONSIDERING ROW 6?

TIA,

Jim

Jim,

I'm missing something here. You're converting the formatting of cell
B6 from Text to Text, then putting in the same text value that was
already there? I have the feeling there is something going on previous
to this.

You mention "Would prefer somehow just removing the " ' " being
programmatically inserted
and maintaining the text format from the source." If the destination
cell is already formatted as Text, then pasting in the source text
should work without the apostrophe.

Incidentally, if you change the second line of your latest example:

Range("B6").Numberformat = "@"
Range("B6").FormulaR1C1 = "0000000000"

to

Range("B6").Value = "0000000000"

you'll get the same result.

Mark, Thanks for your input..
At the source B6 is formatted as text and displays 0000000000.
After copying and Pasting-Special_Values it "lands" in the Destination
Sheet Cell B6 as '0000000000 << which was the Culprit IN THE BEGINNING;
Not sure why this is occuring, IT JUST IS !!
Tks,
Jim May

"Mark Lincoln" wrote:

Jim,

I'm missing something here. You're converting the formatting of cell
B6 from Text to Text, then putting in the same text value that was
already there? I have the feeling there is something going on previous
to this.

You mention "Would prefer somehow just removing the " ' " being
programmatically inserted
and maintaining the text format from the source." If the destination
cell is already formatted as Text, then pasting in the source text
should work without the apostrophe.

Incidentally, if you change the second line of your latest example:

Range("B6").Numberformat = "@"
Range("B6").FormulaR1C1 = "0000000000"

to

Range("B6").Value = "0000000000"

you'll get the same result.

Jim,

Okay, I'm seeing this a bit more clearly now. Maybe because I'm not at
work. :-)

If I copy a cell with a series of zeros stored in a cell formatted as
Text and paste it into another cell, I only get the zeros. No
apostrophe, whether I merely Paste or Paste Special - Values. I'm now
wondering how you get that confounded apostrophe. And am I correct in
assuming it only shows up in B6?

What's strange is that if I don't choose the "Keep Source Formatting"
option after the Paste, the zeros are all still there even though the
destination cell keeps General formatting. And yet, no apostrophe
(Excel 2002). The more I look at this, the confuseder I get - and the
more I want to get to the bottom of this. Which version of Excel are
you using?

Also, do you actually need to Paste Special? That is, are DepAmt and
DepDate also stored as text?

Perhaps you need to Copy/Paste the first two columns normally, then the
third and fourth as Special-Values. Or at least you might need to be
sure your destination cells in column B are formatted as text before
the Paste operation.

A bit of detail:

In Excel 2002, I copy a series of zeros from a cell formatted as Text.

Paste places the zeros in the destination cell and changes its format
to Text. If I then enter a series of zeros in that cell, they all
show.

Paste Special - Values places the zeros in the destination cell and
keeps its format as General. (!?!) I see all the zeros. But if I
enter a series in zeros in that cell - or even activate the cell for
editing but making no changes - I only see a single zero after pressing
Enter.

If you have an earlier version of Excel, it may be inserting the
apostrophe because Paste Special - Values keeps the destination cell's
formatting intact and your destination B6 is in General format. And
the Microsoft programmers hadn't yet figured out how to hide the
apostrophe.

the apostrophe only shows up in Destination cell B6;
I'm running Excel 2003
I appreciate your generous input into this situation
and have copied it off to take to work on Monday,
where the application )at my office)..

I have tried previously formatting the destination sheet B Column
as Text (And Saved) before running Macro again.
YOur recommendation to : Copy/Paste the first two columns normally, then the
third and fourth as Special-Values is very worthwhile;

Appreciate everything,,
Jim May


"Mark Lincoln" wrote:

Jim,

Okay, I'm seeing this a bit more clearly now. Maybe because I'm not at
work. :-)

If I copy a cell with a series of zeros stored in a cell formatted as
Text and paste it into another cell, I only get the zeros. No
apostrophe, whether I merely Paste or Paste Special - Values. I'm now
wondering how you get that confounded apostrophe. And am I correct in
assuming it only shows up in B6?

What's strange is that if I don't choose the "Keep Source Formatting"
option after the Paste, the zeros are all still there even though the
destination cell keeps General formatting. And yet, no apostrophe
(Excel 2002). The more I look at this, the confuseder I get - and the
more I want to get to the bottom of this. Which version of Excel are
you using?

Also, do you actually need to Paste Special? That is, are DepAmt and
DepDate also stored as text?

Perhaps you need to Copy/Paste the first two columns normally, then the
third and fourth as Special-Values. Or at least you might need to be
sure your destination cells in column B are formatted as text before
the Paste operation.