Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Right() error changing columns in macro
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
changing columns | Excel Discussion (Misc queries) | |||
Changing Columns to Rows | Excel Discussion (Misc queries) | |||
Changing cell values between columns with macro | Excel Programming |