If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|