R
Ron
Hi,
I need some help with my code please.
I am attempting to loop though all the cells in a column and delete th
characters within a set of parentheses including the parentheses.
want the user to verify the string before deletion using a message box
I am also open to any suggestions to improve the code.
Thanks.
Ron
Sub DeleteRefNo()
Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim sStart As String
Dim sEnd As String
Dim rCount As Integer
Dim dString As String
Dim length As Integer
rCount = 2
Do
Cells(rCount, 4).Select
SearchString = ActiveCell.Value
SearchChar1 = "("
SearchChar2 = ")"
'cycle through search string
For X = 1 To Len(SearchString)
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)
If sStart <> 0 And sEnd <> 0 Then
length = sEnd - sStart + 1
dString = Mid(SearchString, sStart, length)
MsgBox ("Delete string? " & dString), vbYesNo
If vbYes Then
'????? statement to delete the string in dString ???????
End If
End If
Next X
rCount = rCount + 1
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
Cells(1, 1).Select
End Su
I need some help with my code please.
I am attempting to loop though all the cells in a column and delete th
characters within a set of parentheses including the parentheses.
want the user to verify the string before deletion using a message box
I am also open to any suggestions to improve the code.
Thanks.
Ron
Sub DeleteRefNo()
Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim sStart As String
Dim sEnd As String
Dim rCount As Integer
Dim dString As String
Dim length As Integer
rCount = 2
Do
Cells(rCount, 4).Select
SearchString = ActiveCell.Value
SearchChar1 = "("
SearchChar2 = ")"
'cycle through search string
For X = 1 To Len(SearchString)
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)
If sStart <> 0 And sEnd <> 0 Then
length = sEnd - sStart + 1
dString = Mid(SearchString, sStart, length)
MsgBox ("Delete string? " & dString), vbYesNo
If vbYes Then
'????? statement to delete the string in dString ???????
End If
End If
Next X
rCount = rCount + 1
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
Cells(1, 1).Select
End Su