When Values don't equal Values

[jomili]

I have two possible routines, and I'm wondering why one is different from the other.

If I have a column formatted as Text, and I have a value of 000, it displays as "000".
When I run a vba routine like

Please Login or Register  to view this content.

the resultant value is still "000"

But if I do this:

Please Login or Register  to view this content.

the resultant value is "0"

So value doesn't equal value. How do I get around that?

[JEC.]

First method does not change cell format.
Second method does convert the cell to “value” format

[jomili]

So, based on the last comment, if i want the result to be "000", I'd have to do it like so:

Please Login or Register  to view this content.

that's a much slower operation. Is there an alternative?

[rorya]

But if I do this:

Please Login or Register  to view this content.

the resultant value is "0"

It doesn't do that for me in Excel 2016 32bit or Excel 365 64bit

[TMS]

Both versions leave the format as is for me.

Please Login or Register  to view this content.

[jomili]

Forgot to mention, my "000" comes from a formula, "=TEXT(Z3,"000")". Maybe that's the difference?

[TMS]

So, is the cell formatted as Text, or do you just get Text output from the TEXT function?

[rorya]

Why do you need to go cell by cell, rather than the original bulk operation of:

Please Login or Register  to view this content.

[jomili]

TMS, the cell is not formatted as text, so the formula can work.
Rorya, this is for a specialized macro to convert a filtered range to values.

[rorya]

I’d suggest you format the cells as text just before replacing the values, and also go area by area rather than cell by cell.

[jomili]

Rorya,
I understand what you're saying, but what will that do to any columns that have formulas resulting in a numerical result? If the formula results in a number, and I then format the column as Text, then won't the numbers appear as Text numbers instead of actual numbers?

And I guess it's best if I show my current macro that I'm trying to repair.
BTW, I have macros that turn off screen updating, calculations, etc. that are called by this macro. I took those parts out so it wouldn't confuse anyone.

Please Login or Register  to view this content.

[rorya]

If you don’t know in advance what kind of data you are dealing with, I’d stick to the copy paste:

Please Login or Register  to view this content.

[jomili]

I agree with copy/paste. But I'm not familiar with "Area" in this context. Is that each contiguous visible area, rather than each individual cell? If so, I agree that should be faster.

[jomili]

Oh, and I just noticed your double use of Special Cells to take care of the visible. Smooth idea!

[rorya]

Yes it is. Whether it’s faster will depend on how many of the visible cells are contiguous.

[jomili]

thanks so much for the help and education. I really appreciate it.

[rorya]

Glad we could help.