FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
So far I've been able to put together the following VBA code for replacing a number within the parentheses by 1.
However, if the cell does not contain parentheses, how do I avoid that error. Also, is there a cleaner way to do this.
In the example below, row 3 would change to 11 and row 5, 45, while keeping the parentheses.
However, if the cell does not contain parentheses, how do I avoid that error. Also, is there a cleaner way to do this.
In the example below, row 3 would change to 11 and row 5, 45, while keeping the parentheses.
VBA Code:
Sub Test()
Dim mystr As String
Dim par1CharNum As Long
Dim par2CharNum As Long
Dim MyParseStr As Long
Dim LastRow As Long: LastRow = Sheets("Birthdays").Cells(Rows.Count, "E").End(xlUp).Row
Dim i As Long
For i = 3 To LastRow
mystr = Sheets("Birthdays").Range("E" & i).Value
par1CharNum = InStr(1, mystr, "(")
par2CharNum = InStr(par1CharNum, mystr, ")")
MyParseStr = Mid(mystr, par1CharNum + 1, par2CharNum - par1CharNum - 1)
Sheets("Birthdays").Range("E" & i).Value = Left(mystr, par1CharNum - 2) & " (" & MyParseStr + 1 & ")"
Next i
End Sub
Calendar.xlsm | |||
---|---|---|---|
E | |||
1 | Name | ||
2 | Mickey Mouse | ||
3 | Donald Duck (10) | ||
4 | George Washington | ||
5 | Abraham Lincoln (44) | ||
Sheet2 |