E2007 - Macro for splitting text and then replacing something

Hello,

Could somebody please help me to modify this macro that I recorded with the
macro recorder so that it won't contain any hard-coded columns anymore and
instead uses whatever column is selected as the starting point? This is
always going to be the last colum on the sheet, but unfortunately, the column
number itself changes.
_____

Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="P", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
Columns("O:O").Select
Selection.Replace What:="L", Replacement:="PL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

you can use
activecell.entirecolumn.select

"Niniel" wrote:

Hello,

Could somebody please help me to modify this macro that I recorded with the
macro recorder so that it won't contain any hard-coded columns anymore and
instead uses whatever column is selected as the starting point? This is
always going to be the last colum on the sheet, but unfortunately, the column
number itself changes.
_____

Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="P", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
Columns("O:O").Select
Selection.Replace What:="L", Replacement:="PL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Thank you, that was helpful - in a bit of a round-about way:
Since I could quite figure out how to apply your recommended fix, I
re-recorded the macro with Relative References on, and now it's working.
Strangely, however, I had to change the first offset from (0, 3) to (0, 0),
funny that the recorder would get that wrong (bug?).
Anyway, this is how it looks now:


ActiveCell.Offset(0, 0).Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="P",
FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Replace What:="L", Replacement:="PL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Columns("A:A").EntireColumn.EntireColum n.AutoFit


"Atishoo" wrote:

you can use
activecell.entirecolumn.select

"Niniel" wrote:

Hello,

Could somebody please help me to modify this macro that I recorded with the
macro recorder so that it won't contain any hard-coded columns anymore and
instead uses whatever column is selected as the starting point? This is
always going to be the last colum on the sheet, but unfortunately, the column
number itself changes.
_____

Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="P", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
Columns("O:O").Select
Selection.Replace What:="L", Replacement:="PL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With