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

--
Message posted from http://www.ExcelForum.com

Ron,

Insert these 2 lines

SearchString = Left(SearchString, sStart - 1) & Right(SearchString,
Len(SearchString) - sEnd)
ActiveCell.Value = SearchString


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron " wrote in message
...
Hi,

I need some help with my code please.
I am attempting to loop though all the cells in a column and delete the
characters within a set of parentheses including the parentheses. I
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 Sub


---
Message posted from http://www.ExcelForum.com/

Sub DeleteRefNo()

Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim sStart As Long
Dim sEnd As Long
Dim sStrL As String
Dim sStrR As String
Dim cell As Range
Dim rCount As Integer
Dim dString As String

rCount = 2

SearchChar1 = "("
SearchChar2 = ")"
sPattern = "*" & SearchChar1 & "*" & SearchChar2 & "*"

Do

Set cell = Cells(rCount, 4)
SearchString = cell.Value


If SearchString Like sPattern Then
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)

sStrL = Left(SearchString, sStart)
sStrR = Mid(SearchString, sEnd)
dString = Mid(SearchString, sStart, sEnd - sStart + 1)

MsgBox "Delete string? " & dString, vbYesNo
If vbYes Then
cell.Value = Left(sStrL, Len(sStrL) - 1) & _
Right(sStrR, Len(sStrR) - 1)
End If
End If


rCount = rCount + 1

Loop Until IsEmpty(cell.Offset(1, 0))

Cells(1, 1).Select

End Sub


--
Regards,
Tom Ogilvy


"Ron " wrote in message
...
Hi,

I need some help with my code please.
I am attempting to loop though all the cells in a column and delete the
characters within a set of parentheses including the parentheses. I
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 Sub


---
Message posted from http://www.ExcelForum.com/

Would the "Replace" function be better ?

myVar = "this is a test"
newVar = Replace(myVar, "test", "")
MsgBox newVar

Macroman


"Tom Ogilvy" wrote in message
...
Sub DeleteRefNo()

Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim sStart As Long
Dim sEnd As Long
Dim sStrL As String
Dim sStrR As String
Dim cell As Range
Dim rCount As Integer
Dim dString As String

rCount = 2

SearchChar1 = "("
SearchChar2 = ")"
sPattern = "*" & SearchChar1 & "*" & SearchChar2 & "*"

Do

Set cell = Cells(rCount, 4)
SearchString = cell.Value


If SearchString Like sPattern Then
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)

sStrL = Left(SearchString, sStart)
sStrR = Mid(SearchString, sEnd)
dString = Mid(SearchString, sStart, sEnd - sStart + 1)

MsgBox "Delete string? " & dString, vbYesNo
If vbYes Then
cell.Value = Left(sStrL, Len(sStrL) - 1) & _
Right(sStrR, Len(sStrR) - 1)
End If
End If


rCount = rCount + 1

Loop Until IsEmpty(cell.Offset(1, 0))

Cells(1, 1).Select

End Sub


--
Regards,
Tom Ogilvy


"Ron " wrote in message
...
Hi,

I need some help with my code please.
I am attempting to loop though all the cells in a column and delete the
characters within a set of parentheses including the parentheses. I
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 Sub


---
Message posted from http://www.ExcelForum.com/

Probably not because you would need to construct the string to replace in a
very similar way to constructing the string without that part. Interesting
alternative, but not 'better' (IMO).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Macroman" wrote in message
...
Would the "Replace" function be better ?

myVar = "this is a test"
newVar = Replace(myVar, "test", "")
MsgBox newVar

Macroman


"Tom Ogilvy" wrote in message
...
Sub DeleteRefNo()

Dim SearchString As String
Dim SearchChar1 As String
Dim SearchChar2 As String
Dim sStart As Long
Dim sEnd As Long
Dim sStrL As String
Dim sStrR As String
Dim cell As Range
Dim rCount As Integer
Dim dString As String

rCount = 2

SearchChar1 = "("
SearchChar2 = ")"
sPattern = "*" & SearchChar1 & "*" & SearchChar2 & "*"

Do

Set cell = Cells(rCount, 4)
SearchString = cell.Value


If SearchString Like sPattern Then
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)

sStrL = Left(SearchString, sStart)
sStrR = Mid(SearchString, sEnd)
dString = Mid(SearchString, sStart, sEnd - sStart + 1)

MsgBox "Delete string? " & dString, vbYesNo
If vbYes Then
cell.Value = Left(sStrL, Len(sStrL) - 1) & _
Right(sStrR, Len(sStrR) - 1)
End If
End If


rCount = rCount + 1

Loop Until IsEmpty(cell.Offset(1, 0))

Cells(1, 1).Select

End Sub


--
Regards,
Tom Ogilvy


"Ron " wrote in message
...
Hi,

I need some help with my code please.
I am attempting to loop though all the cells in a column and delete
the
characters within a set of parentheses including the parentheses. I
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 Sub


---
Message posted from http://www.ExcelForum.com/

Ron wrote in
:

sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)


You want to find things BETWEEN parentheses, right?

So you should use this:
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(sStart, SearchString, SearchChar2, 1)


--
My email address has an extra @ (spell it out) and an extra invalid. Please
remove them if you are not a spammer or list broker and want to reply.

if the string might contain

"abc) cde ( efg ) hij"

your approach might make sense - otherwise, it doesn't make a difference.

--
Regards,
Tom Ogilvy


"Jonathan Rynd" wrote in message
. ..
Ron wrote in
:

sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(1, SearchString, SearchChar2, 1)


You want to find things BETWEEN parentheses, right?

So you should use this:
sStart = InStr(1, SearchString, SearchChar1, 1)
sEnd = InStr(sStart, SearchString, SearchChar2, 1)


--
My email address has an extra @ (spell it out) and an extra invalid.
Please
remove them if you are not a spammer or list broker and want to reply.

Thanks for everyone’s prompt response. I’m new at this so it’s ver
interesting and helpful to see several options and the discussions

--
Message posted from http://www.ExcelForum.com