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)

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.

"S Himmelrich" wrote:

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)

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



S Himmelrich wrote:

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)

--

Dave Peterson

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.

--
(please remember to click yes if replies you receive are helpful to you)


"S Himmelrich" wrote:

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)

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



On Dec 12, 11:34 am, Dave Peterson wrote:
You need to loop through the cells if you want to use right().

dim myCell as range
dim myRng as range
with worksheets("somesheetnamehere")

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

S Himmelrich wrote:

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)

--

Dave Peterson

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))"




On Dec 12, 11:41 am, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)



"S Himmelrich" wrote:
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)- Hide quoted text -

- Show quoted text -

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.



--
(please remember to click yes if replies you receive are helpful to you)


"S Himmelrich" wrote:

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))"




On Dec 12, 11:41 am, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)



"S Himmelrich" wrote:
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)- Hide quoted text -

- Show quoted text -

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.
--
(please remember to click yes if replies you receive are helpful to you)


"S Himmelrich" wrote:

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



On Dec 12, 12:36 pm, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)



"S Himmelrich" wrote:
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))"

On Dec 12, 11:41 am, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)

"S Himmelrich" wrote:
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)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.
--
(please remember to click yes if replies you receive are helpful to you)


"SeanC UK" wrote:

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.
--
(please remember to click yes if replies you receive are helpful to you)


"S Himmelrich" wrote:

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



On Dec 12, 12:36 pm, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)



"S Himmelrich" wrote:
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))"

On Dec 12, 11:41 am, SeanC UK
wrote:
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.

--
(please remember to click yes if replies you receive are helpful to you)

"S Himmelrich" wrote:
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)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -