Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 VBA Replacements
I have the following code to replace two values in my sheet:
Public Sub Replace_Y_With_Rate5() For Each cell In Selection cell.Value = Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare) cell.Value = Replace(cell.Value, "CP", "8", 1, 1, vbTextCompare) Next cell End Sub I'm wondering if I could combine these actions into one calculation (and how to do so); reason being I have another macro that is executed after each worksheet calculation to display a pop-up message. If the above macro is executed I receive two pop-ups (because of the separate lines = separate calculations, I believe) and want only one (pop-up to be displayed). Thanks, CVinje |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 VBA Replacements
You could stop the call to the worksheet_change/worksheet_calculate event:
Public Sub Replace_Y_With_Rate5() application.enableevents = false For Each cell In Selection cell.Value = Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare) cell.Value = Replace(cell.Value, "CP", "8", 1, 1, vbTextCompare) Next cell application.enableevents = true End Sub or even just plop the new value back just once: Public Sub Replace_Y_With_Rate5() dim myVal as variant For Each cell In Selection myval = cell.value myval = Replace(myval, "Y", "24", 1, 1, vbTextCompare) myval = Replace(myval, "CP", "8", 1, 1, vbTextCompare) 'and check if myval < cell.value then application.enableevents = false cell.value = myval application.enableevents = true end if Next cell End Sub CVinje wrote: I have the following code to replace two values in my sheet: Public Sub Replace_Y_With_Rate5() For Each cell In Selection cell.Value = Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare) cell.Value = Replace(cell.Value, "CP", "8", 1, 1, vbTextCompare) Next cell End Sub I'm wondering if I could combine these actions into one calculation (and how to do so); reason being I have another macro that is executed after each worksheet calculation to display a pop-up message. If the above macro is executed I receive two pop-ups (because of the separate lines = separate calculations, I believe) and want only one (pop-up to be displayed). Thanks, CVinje -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 VBA Replacements
cell.Value = Replace(Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare), _
"CP", "8", 1, 1, vbTextCompare) Regards, Stefi €žCVinje€ť ezt Ă*rta: I have the following code to replace two values in my sheet: Public Sub Replace_Y_With_Rate5() For Each cell In Selection cell.Value = Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare) cell.Value = Replace(cell.Value, "CP", "8", 1, 1, vbTextCompare) Next cell End Sub I'm wondering if I could combine these actions into one calculation (and how to do so); reason being I have another macro that is executed after each worksheet calculation to display a pop-up message. If the above macro is executed I receive two pop-ups (because of the separate lines = separate calculations, I believe) and want only one (pop-up to be displayed). Thanks, CVinje |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine worksheets w/o enough rows to combine? | Excel Worksheet Functions | |||
Combine cells with the same reference and combine quantities | Excel Discussion (Misc queries) | |||
combine or if | Excel Worksheet Functions | |||
Combine into one please. | Excel Worksheet Functions | |||
Stop excel from reporting replacements | Excel Discussion (Misc queries) |