Below is code that works to add one !
to one or more selection of cells but how do I add one subsequent !
and then remove !!
after?
There are two sequences:
shift1<
to shift1!<
to shift1!!<
back to shift1<
shift1<^
to shift1!<^
to shift1!!<^
back to shift1<^
'successfully adds ! to any selection of blank cells or after any string
'example: from "blank cell" to "!" or from "shift1" to "shift1!"
If InStr(ActiveCell.Value, "!") = 0 And InStr(ActiveCell.Value, "^") = 0 Then
If InStr(ActiveCell.Value, "<") = 0 And InStr(ActiveCell.Value, ">") = 0 _
And InStr(ActiveCell.Value, "^") = 0 Then
For Each c In Selection.Cells
c.Value = c.Value & "!"
Next c
'successfully adds ! in between shift string and < if ! is not present
'example: from "shift1<" to "shift1!<"
ElseIf InStr(ActiveCell.Value, "!") = 0 Then
Selection.Cells.Replace "<", "!<"
'does not add another ! in between shift string and < if one ! is present
'example: from "shift1!<" to "shift1!!<"
ElseIf InStr(ActiveCell.Value, "!") = 1 Then
Selection.Cells.Replace "!<", "!!<"
'does not remove !! in between shift string and < if two ! are present
'example: from "shift1!!<" to "shift1<"
ElseIf InStr(ActiveCell.Value, "!") = 2 Then
Selection.Cells.Replace "!!<", "<"
End If
'successfully adds ! in between shift string and <^
'example: from "shift1<^" to "shift1!<^"
ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
And InStr(ActiveCell.Value, "!") = 0 Then
Selection.Cells.Replace "<^", "!<^"
'does not add another ! in between shift string and <^
'example: from "shift1!<^" to "shift1!!<^"
ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
And InStr(ActiveCell.Value, "!") = 1 Then
Selection.Cells.Replace "!<^", "!!<^"
'does not remove !! in between shift string and < if two ! are present
'example: from "shift1!!<^" to "shift1<^"
ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
And InStr(ActiveCell.Value, "!") = 2 Then
Selection.Cells.Replace "!!<^", "<^"
End If
Your two cases are essentially the same. It doesn't matter if there is a ^
after <
if you only change things before the <
.
Try:
If InStr(ActiveCell.Value, "!") = 0 Then
Selection.Cells.Replace "<", "!<"
ElseIf InStr(ActiveCell.Value, "!!<") > 0 Then
Selection.Cells.Replace "!!<", "<"
ElseIf InStr(ActiveCell.Value, "!<") > 0 Then
Selection.Cells.Replace "!<", "!!<"
End If