nyconfidential
New Member
- Joined
- Jul 22, 2015
- Messages
- 49
- Office Version
- 365
- 2016
I was asked to find and replace pound signs with a percentage sign in a particular workbook. Rather than doing find and replace all, I want to loop through all worksheets in VBA and see if the first character in each cell is a pound sign, remove that pound sign and replace it with a percentage sign(I just want to replace a pound sign if it is the FIRST character in the cell string).
The code below accomplishes my goal, but there are 65 worksheets in this workbook, and some of the worksheets have pound signs in over 5000 cells, so it takes almost 10 minutes to complete. Can anyone tell me if there is a way to optimize my code below? Is looping through each cell in the range the smartest way to do this?(Apologies, I come from an Access background and am accustomed to looping through recordsets when updating values). Appreciate any input.
The code below accomplishes my goal, but there are 65 worksheets in this workbook, and some of the worksheets have pound signs in over 5000 cells, so it takes almost 10 minutes to complete. Can anyone tell me if there is a way to optimize my code below? Is looping through each cell in the range the smartest way to do this?(Apologies, I come from an Access background and am accustomed to looping through recordsets when updating values). Appreciate any input.
Code:
Public Sub ReplaceWorksheetValues()
On Error Resume Next
Dim WrkSht As Worksheet
Dim rCell As Range
Dim rRng As Range
For Each WrkSht In ActiveWorkbook.Worksheets
Range("A1").Select
Application.StatusBar = "Updating " & WrkSht.Name & ", please wait..."
Set rRng = WrkSht.UsedRange
For Each rCell In rRng.Cells
'Debug.Print rCell.Address, rCell.Value
'Find cell values that start with a pound sign
If Left(rCell.Value, 1) = "#" Then
'Strip the first character(pound sign), prepend an equals sign to the start of the string
rCell.Value = "%" & Right(rCell.Value, Len(rCell.Value) - 1)
End If
Next rCell
Application.StatusBar = "Updating " & Str(WrkSht.Name)
Next WrkSht
End Sub