Right() error changing columns in macro

[Rick Rothstein \(MVP - VB\)]

Just out of curiosity, when your text is falling short of the 6 characters
you expect, is it perhaps leading zeroes that are being dropped? If so, are
you perhaps assigning the result from the Right function call to a numerical
type variable (as opposed to a String type)?

Rick

 

[John Bundy]

Sorry to hit this up late but i have 2 ideas, first trim it with
rngCells.Value = Right(trim(rngCells.Value), 6)
if that doesn't work try a clean inside of that, something like
rngCells.Value = Right(trim(clean(rngCells.Value)), 6) as it is 'possible'
there are some screwy characters in there. The next thing to do is find a
place where the macro failed, then on the original enter take it one step at
a time. Do a Len(cell) and see if the result is consistent with what you see,
next do a Len(Trim(cell)) and see if it changes, as well as if that is
consistent with what you see. If all does not seem right, try
Len(Trim(Clean(cell))). If all is well then add a Right to the front, if not,
send me the workbook. (e-mail address removed)(remove)

 

[SeanC UK]

Can you give some examples of the contents of the cells that are not working?
It may be due to the data in the cell. For instance, if I have a number in my
cell:

10045678

and run the code then the result will be:

45678

because Excel sees it as a number and does not display the leading zeros. If
this is the problem you are having then you will need to format the cells in
some way. Formatting as numbers with leading zeros or as text should do the
trick.

If it is not a problem with numbers then please supply examples of your data.

Sean.

 

[SeanC UK]

My apologies, I didn't see this second page of replies before I added my last
reply (using Firefox, I get my listing a page at a time), so you may have
found your answer.

Having said that, looking at the example of your data then my last reply may
answer the question anyway, as Right(text, 6) on your data with a cell
formatted as General, or Number, would leave the leading zeros off the result
- from a brief inspection, I haven't tested.

Try adding

Columns("B:B").NumberFormat = "@"

before the loop begins to format the column as text.

Sean.