Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
E2007 - Macro for splitting text and then replacing something
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
E2007 - Macro for splitting text and then replacing something
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - Fixed text code needs replacing with variable text | Excel Discussion (Misc queries) | |||
splitting cell text using macro | Excel Discussion (Misc queries) | |||
Macro replacing links | Excel Discussion (Misc queries) | |||
replacing macro? | Excel Discussion (Misc queries) | |||
Macro help:replacing figures | Excel Discussion (Misc queries) |