Combine 2 VBA Replacements

  • Thread starter Thread starter CVinje
  • Start date Start date
C

CVinje

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
 
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
 
cell.Value = Replace(Replace(cell.Value, "Y", "24", 1, 1, vbTextCompare), _
"CP", "8", 1, 1, vbTextCompare)
Regards,
Stefi

„CVinje†ezt írta:
 
Back
Top