Right() error changing columns in macro

[S Himmelrich]

I get an error when I run this in a macro...what can I do.

' Convert account column
Columns("B:B").Select
Selection.Value = Right(Range("B:B").Value, 6)

 

[JLGWhiz]

I don't believe I have ever seen the Right function work on an entire column.
You will probably have to use a For ... Next statement and check one cell at
the time.

 

[Dave Peterson]

You need to loop through the cells if you want to use right().

dim myCell as range
dim myRng as range
with worksheets("somesheetnamehere")
set myrng = .range("B1", .cells(.rows.count,"B").end(xlup))
end with
for each mycell in myrng.cells
mycell.value = right(mycell.value,6)
next mycell

=========
You may want to record a macro when you:
select column B
data|Text to columns|Fixed width
and draw a line after the 6th character
and do not import the field(s) to the right of that line

 

[SeanC UK]

Hi S,

Can try:

Dim rngCells As Range
With ActiveSheet
For Each rngCells In Range(Cells(1, 2), Cells(.UsedRange.Rows.Count, 2))
rngCells.Value = Right(rngCells.Value, 6)
Next
End With


Sean.

 

[S Himmelrich]

I'm getting invalid command error on line:
set myrng = .range("B1", .cells(.rows.count,"B").end(xlup))

 

[S Himmelrich]

We got closer with this one, but I'm getting some rows replacing with
less characters. Basically I should be searching B3 to last cell in
column be and replacing with last six characters.....I'm not that good
with VB so I'm not clear on what I'd change in this statement "For
Each rngCells In Range(Cells(1, 2), Cells(.UsedRange.Rows.Count, 2))"

 

[SeanC UK]

Hi,

the reference:

Range(Cells(1, 2), Cells(.UsedRange.Rows.Count, 2))

can be seen as:

Range(TopLeftCell,BottomRightCell)

where I have defined the top left and bottom right cells using:

Cells(Row Number, Column Number)

So to start from Row 3 Column B to the last cell in column B change the code
to:

Range(Cells(3, 2), Cells(.UsedRange.Rows.Count, 2))

The code you used - Right(Text,6) will replace all the cells with just the
last 6 characters in each cell.

I hope this helps,

Sean.

 

[Dave Peterson]

You changed something else in the code and didn't post that portion.

 

[S Himmelrich]

Nope, no changes.

You changed something else in the code and didn't post that portion.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[S Himmelrich]

here is what I've got in my VB, which is working, but one very odd
thing.......not all cells are being parsed with the last 6 characters
- some are 4 or 5 - any thoughts?


Dim rngCells As Range
With ActiveSheet
For Each rngCells In Range(Cells(3, 2), Cells(.UsedRange.Rows.Count,
2))
rngCells.Value = Right(rngCells.Value, 6)
Next
End With

 

[SeanC UK]

Do these cells definitely start with 6 or more characters? The Right function
will only return 6 characters when the cell starts with 6 or more. If there
are fewer than 6 then the cell contents will not change. If you are finding
that the cells start with greater than 6, but are being left with 4 or 5
after then please give more details of the cell contents.

Sean.

 

[Rick Rothstein \(MVP - VB\)]

Do all your cells, in fact, have 6 or more characters in them? If so, do any
of those cells have one or more spaces at the beginning of the last 6
character positions (in other words, do you have 6 characters, but simply
can't see the first 1 or 2 because they are blank spaces)?

Rick

 

[JLGWhiz]

Just FYI, the only thing that does not count as a character in the Left,
Right and Mid functions is "" (null). Everything else will be counted as a
character whether you see it or not. So if someone entered three spaces
after a text entry, the 6 would only return three text characters plus the
three spaces. To avoid these conditions, you would incorporate the Trim
function into your code.

RngCells.Value = Right(RTrim(RngCells.Value, 6)

 

[S Himmelrich]

All cells have this format: 3180015155 002075

so there are options....like removing the first 11 characters...but
all are in this format so I'm not sure why the last six are not being
picked up?

 

[S Himmelrich]

thank you for responding......here is more information regarding my
problem.

All cells have this format: 3180015155 002075

so there are options....like removing the first 11 characters...but
all are in this format so I'm not sure why the last six are not being
picked up?

Also the statement you provided is incorrect syntax so I'm not sure
exactly what to do with below...I understand what you are trying to
do.

Thanks for responding.

 

[S Himmelrich]

All cells have this format: 3180015155 002075

so there are options....like removing the first 11 characters...but
all are in this format so I'm not sure why the last six are not being
picked up?

thanks for your help

 

[Dave Peterson]

Try deleting that line and retyping it.

Maybe you got some invalid characters in your copy|paste.

If it doesn't work, post the current version.

 

[S Himmelrich]

I get a compile error highlighting ".Rows in line 4, I've commented
out line three as I'm on the current worksheet to be changed.



Dim myCell As Range
Dim myRng As Range
'With Worksheets("somesheetnamehere")
Set myRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each myCell In myRng.Cells
myCell.Value = Right(myCell.Value, 6)
Next myCell

 

[S Himmelrich]

UPDATE: if I trim 8 instead of 6 it works....this must be something
with the original data.....

 

[Dave Peterson]

You can't just comment that line and expect it to work.

Change it to:
with activesheet