|
Macro Only works Once
Hi all,
I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? |
Macro Only works Once
Hi Don,
That's a good idea to shorten the code, but has no effect on the problem I am encountering. Don Guillett wrote: try this idea Sub replaceletters() With Selection For i = 97 To 122 .Replace what:=Chr(i), replacement:="" 'a-z .Replace what:=Chr(42), replacement:="" '* .Replace what:=Chr(45), replacement:="" '- Next i End With End Sub -- Don Guillett SalesAid Software wrote in message ups.com... Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? |
Macro Only works Once
Hi JLGWhiz
That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? |
Macro Only works Once
Since your code runs against the current selection, maybe you don't have the
correct stuff selected when you click the button????????? " wrote: Hi JLGWhiz That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? -- Dave Peterson |
Macro Only works Once
Hi Dave,
No that's not the problem. If I add Range("B3:B14").Select (for example) before the rest of the code, it still doesn't work, however if I close the work book, and then open it again, the code works once, but not again. Dave Peterson wrote: Since your code runs against the current selection, maybe you don't have the correct stuff selected when you click the button????????? " wrote: Hi JLGWhiz That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? -- Dave Peterson |
Macro Only works Once
Replace and Find like to remember the last settings that you've used--either in
code or manually. Try adding all the parms to your .replace statement that you want. Don't trust that the existing parms are what you need for your .replace. expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat) " wrote: Hi Dave, No that's not the problem. If I add Range("B3:B14").Select (for example) before the rest of the code, it still doesn't work, however if I close the work book, and then open it again, the code works once, but not again. Dave Peterson wrote: Since your code runs against the current selection, maybe you don't have the correct stuff selected when you click the button????????? " wrote: Hi JLGWhiz That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? -- Dave Peterson -- Dave Peterson |
Macro Only works Once
Hi Dave,
That was it, the problem was the last line of code caused it to look at Whole, but for most of the code I actually only wanted it to look at any part. Thanks. My revised code is now: Sub RemoveLetters() Dim intChar As Integer With Selection For intChar = 97 To 122 .Replace What:=Chr(intChar), Replacement:="", MatchCase:=False, LookAt:=xlPart 'a-z Next intChar .Replace What:="~*", Replacement:="" .Replace What:="--*", Replacement:="" .Replace What:="-", Replacement:="0", LookAt:=xlWhole End With End Sub On Dec 28, 3:18 pm, Dave Peterson wrote: Replace and Find like to remember the last settings that you've used--either in code or manually. Try adding all the parms to your .replace statement that you want. Don't trust that the existing parms are what you need for your .replace. expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat) " wrote: Hi Dave, No that's not the problem. If I add Range("B3:B14").Select (for example) before the rest of the code, it still doesn't work, however if I close the work book, and then open it again, the code works once, but not again. Dave Peterson wrote: Since your code runs against the current selection, maybe you don't have the correct stuff selected when you click the button????????? " wrote: Hi JLGWhiz That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? -- Dave Peterson-- Dave Peterson |
Macro Only works Once
I would still specify all the parms on each .replace.
" wrote: Hi Dave, That was it, the problem was the last line of code caused it to look at Whole, but for most of the code I actually only wanted it to look at any part. Thanks. My revised code is now: Sub RemoveLetters() Dim intChar As Integer With Selection For intChar = 97 To 122 .Replace What:=Chr(intChar), Replacement:="", MatchCase:=False, LookAt:=xlPart 'a-z Next intChar .Replace What:="~*", Replacement:="" .Replace What:="--*", Replacement:="" .Replace What:="-", Replacement:="0", LookAt:=xlWhole End With End Sub On Dec 28, 3:18 pm, Dave Peterson wrote: Replace and Find like to remember the last settings that you've used--either in code or manually. Try adding all the parms to your .replace statement that you want. Don't trust that the existing parms are what you need for your .replace. expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat) " wrote: Hi Dave, No that's not the problem. If I add Range("B3:B14").Select (for example) before the rest of the code, it still doesn't work, however if I close the work book, and then open it again, the code works once, but not again. Dave Peterson wrote: Since your code runs against the current selection, maybe you don't have the correct stuff selected when you click the button????????? " wrote: Hi JLGWhiz That is not true, I call the macro, by having it assigned to a button on the tool bar. JLGWhiz wrote: There is nothing in the macro you posted to make it run at all. So, you must have another macro in the ThisWorkbook code module that is triggered with the Workbooks_Open() function. If that is true, then to get it to run again, without reopening the workbook, you would have to set up a different control, i.e. command button or keyboard shortcut, to initiate the macro. " wrote: Hi all, I have a problem with macros. I have a macro that used to work as desired, and now it only works the first time Excel is opened, then it doesn't run at all: Sub RemoveLetters() Selection.Replace What:="a", Replacement:="", MatchCase:=False Selection.Replace What:="b", Replacement:="", MatchCase:=False Selection.Replace What:="c", Replacement:="", MatchCase:=False Selection.Replace What:="d", Replacement:="", MatchCase:=False Selection.Replace What:="e", Replacement:="", MatchCase:=False Selection.Replace What:="f", Replacement:="", MatchCase:=False Selection.Replace What:="g", Replacement:="", MatchCase:=False Selection.Replace What:="h", Replacement:="", MatchCase:=False Selection.Replace What:="i", Replacement:="", MatchCase:=False Selection.Replace What:="j", Replacement:="", MatchCase:=False Selection.Replace What:="k", Replacement:="", MatchCase:=False Selection.Replace What:="l", Replacement:="", MatchCase:=False Selection.Replace What:="m", Replacement:="", MatchCase:=False Selection.Replace What:="n", Replacement:="", MatchCase:=False Selection.Replace What:="o", Replacement:="", MatchCase:=False Selection.Replace What:="p", Replacement:="", MatchCase:=False Selection.Replace What:="q", Replacement:="", MatchCase:=False Selection.Replace What:="r", Replacement:="", MatchCase:=False Selection.Replace What:="s", Replacement:="", MatchCase:=False Selection.Replace What:="t", Replacement:="", MatchCase:=False Selection.Replace What:="u", Replacement:="", MatchCase:=False Selection.Replace What:="v", Replacement:="", MatchCase:=False Selection.Replace What:="w", Replacement:="", MatchCase:=False Selection.Replace What:="x", Replacement:="", MatchCase:=False Selection.Replace What:="y", Replacement:="", MatchCase:=False Selection.Replace What:="z", Replacement:="", MatchCase:=False Selection.Replace What:="~*", Replacement:="", MatchCase:=False Selection.Replace What:="--*", Replacement:="", MatchCase:=False Selection.Replace What:="-", Replacement:="0", LookAt:=xlWhole, MatchCase:=False End Sub If I close Excel, and then open it again, the macro works once, then doesn't work again, until I close and reopen Excel. Any idea why this is? -- Dave Peterson-- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com