Help with number formatting

[carlito2002wgn]

Hi,

I am using the following macro to get data from one workbook, and copy it to another:

Please Login or Register  to view this content.

In the last part of my code, I am trying to apply the number format of "00000000", but it doesn't seem to be working. If I click on a cell with the imported data, there are no leading zeros in the formula bar.

Help please!

Thank you,
Carlos

[TMS]

there are no leading zeros in the formula bar.

There won't be. Formatting is about what you see displayed in the cell, not the value of the cell. So, 1234 will always show in the formula bar as 1234, even though it is displayed as 00001234 with a Custom Format of "00000000".

[carlito2002wgn]

Hi TMS. Thank you for the quick reply.

The thing is, the source workbook from where I am "getting" the numbers has leading zeros, both in the cells, and in the formula bar. I need the leading zeros to come over to the second workbook from the first one. Without them, my lookup formulas are not working.

Can my above code be modified to pull the numbers with the leading zeros?

Thank you,
Carlos

[Roel Jongman]

Change the .formula to .value or .text

I think using .value will do the trick. but if there are still problems then use .text

Please Login or Register  to view this content.

[carlito2002wgn]

Hi Roel,

Thanks for the reply.

Unfortunately, neither .value or .text worked. Actually, .text threw an error.

So frustrating!

I am a vba dummy, so I don't know what I am doing, but I tried changing the first .value to .copy, and the second .value to a paste special values, but that just made Excel go blank and I had to kill it via task manager

[Fluff13]

Try

Please Login or Register  to view this content.

[TMS]

Ok, if you're seeing leading zeroes in the formula bar, then the cell is formatted as text, or has a leading apostrophe to force the number into text format. Sometimes data comes from other sources, for example, a web site, and appears as text although the cell is formatted as numeric or general. Not sure why that happens, but it does, possibly the encoding.

[carlito2002wgn]

Hi Fluff,

When I try .Value and .Text, nothing pulls into the workbook. I don't get any error messages or anything, but nothing pulls in

[carlito2002wgn]

I just found out something by chance. In my source file exist all of the numbers with leading zeros. The formatting is "General".

Here is what I found out: If I click in a cell with one of the numbers with leading zeros, and edit it in any way, and then hit enter, the leading zeros disappear.

For example, if the number is 00010268, and I click in the cell, and only change the 2 to a 3, when I click out of it, or hit enter, the leading zeros disappear.

Why is this happening?

[TMS]

You've just converted the format to numeric or general.

[carlito2002wgn]

There has to be some way to do this. If I do a look up from my workbook to my source workbook, I get matches.

When I pull in the data from my source workbook into my workbook via the above macro, and then try to do lookups, they don't work.

The formatting is such that the lookups work when the data is in two different workbooks, but when I run the macro and pull in the data from source workbook to workbook, the formatting (or something) changes such that the lookups no longer work. Even though I am doing the same lookups!

[carlito2002wgn]

I could not find a solution to the above problem, but I found a workaround that suits my needs fine.

Thanks to everyone who spent time trying to help me!