So please see my attached workbook.
Columns E/F/H and I show leading zeros
That is also what I need.
However E F and H only show the leading zero but don't actually have it....
So thats screwing up my lookups.
How can I make sure the leading zero's become "physical" like column I?
Thanks.
format cells as number
Maybe:
Sub rpinxt() Dim rcell As Range For Each rcell In Range("E1:E" & Range("E" & Rows.Count).End(3).Row) rcell.Value = "'0" & rcell.Value Next rcell For Each rcell In Range("F1:F" & Range("E" & Rows.Count).End(3).Row) rcell.Value = "'00" & rcell.Value Next rcell For Each rcell In Range("H1:H" & Range("F" & Rows.Count).End(3).Row) rcell.Value = "'000" & rcell.Value Next rcell End Sub
Based on a lot of the questions that I see here, this illustrates a couple of those spreadsheet programming concepts that we need to somehow come to understand in order to be effective spreadsheet programmers.
On the one hand, in column E, F, and H, we have the difference between "actual cell value" and "formatted, displayed value". When the actual value is different from what we see (in your case, the leading, insignificant zeros are not actually part of the cell's value), that can create confusion.
On the other hand, we also have the distinction between "numbers stored as numbers" (columns E, F, H) and "numbers stored as text" (column I). Text is the best data type for a "what you see is what you get" kind of display. However, this forum is full of the kinds of problems that can be encountered from "numbers stored as text". For example, the text string "2" is greater than the text string "10". Because Excel treats numbers stored as text differently than it treats numbers stored as numbers, you get a lot of unexpected behavior.
Part of your task as a programmer is to decide which "data type" will be most useful. If column I is really your goal (to have numbers stored as text strings), then you will need to get used to working with them. Hand entered data will need to be preceeded by an apostrophe, so Excel will recognize the number as a text string. Any values that are calcluated from other values will need to be nested inside of the =TEXT() function (to convert the numbers to text) https://support.office.com/en-us/art...rs=en-US&ad=US You will also need to become familiar with the pitfalls around storing numbers as text, so that you can deal with those cases as well.
Originally Posted by shg
Sorry for the late response.
Thanks JOHN H. DAVIS worked like a charm.
You're welcome. Glad to help out and thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks