Slow loop processing

[nyconfidential]

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.

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

 

[Nine Zero]

is your data only in Column A going down?

 

[nyconfidential]

Nah, it's all over the place, unfortunately. Were you going to suggest putting it into an array if it was only in one column?

 

[JoeMo]

See if this speeds things up on a copies of two or three of your worksheets.

Public Sub ReplaceWorksheetValues()
Dim WrkSht As Worksheet
Dim rCell As Range
Dim rRng As Range
Dim V As Variant, i As Long, j As Long
For Each WrkSht In ActiveWorkbook.Worksheets
    Set rRng = WrkSht.UsedRange
    V = rRng.Value
    For i = LBound(V, 1) To UBound(V, 1)
        For j = LBound(V, 2) To UBound(V, 2)
            If Left(V(i, j), 1) = "#" Then V(i, j) = Replace(V(i, j), "#", "%", 1, 1)
        Next j
    Next i
    rRng.Value = V
Next WrkSht
End Sub

 

[nyconfidential]

Will give it a shot, thanks Joe!!

 

[Fluff]

Does this work for you on the active sheet

Sub chk()
With ActiveSheet.UsedRange
   .Value = Evaluate(Replace("if(left(@,1)=""#"",""%"" & right(@,len(@)-1),@)", "@", .Address))
End With
End Sub

& is it any quicker?

 

[Joe4]

You can also help speed up your code by shutting off Screen Updating and Calculations until the end.
So put this at the top of your code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and then turn them on at the end of your code (after your last Next statement) like this:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[nyconfidential]

Joe, this is amazing - processing time went from minutes to seconds, thank you!! Can you explain why putting the range values into that "V" array is so much faster than looping through the cells?

 

[JoeMo]

Joe, this is amazing - processing time went from minutes to seconds, thank you!! Can you explain why putting the range values into that "V" array is so much faster than looping through the cells?

You're welcome. Placing the values in an array allows looping through the array elements rather than the cells of the worksheet. This is all done in memory which is quite fast compared to looping through cells, as you have observed.

Note too, that you don't need to turn off screen updating with this approach b/c all the action on the active sheet takes place in one write of the altered array to the used range.

 

[nyconfidential]

Does this work for you on the active sheet

Sub chk()
With ActiveSheet.UsedRange
   .Value = Evaluate(Replace("if(left(@,1)=""#"",""%"" & right(@,len(@)-1),@)", "@", .Address))
End With
End Sub

& is it any quicker?

Hi Fluff - wow, this is much faster as well, less code to boot. Did a little reading on the Evaluate function, will read a lot more on it now, thank you!!