I have a sheet full of numbers, intermixed with other data, and need to
change certain cells based on a single criteria.

If the cell contains the character : then I need to add 0: to the beginning
of the current string in that cell.

Can anyone help me with a concise bit of code that will search one sheet and
change those selected cells?

Thank you in advance.

Sub colonate()
Dim s As String
For Each r In ActiveSheet.UsedRange
s = r.Value
If InStr(s, ":") 0 Then
r.Value = "0:" & s
End If
Next
End Sub

--
Gary''s Student - gsnu200718

Sub AAA()
Dim cell As Range
For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
Debug.Print cell.Address, cell.Text
If InStr(1, cell.Value, ":", vbTextCompare) Then
cell.Value = "'0:" & cell.Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy




"Mahnian" wrote:

I have a sheet full of numbers, intermixed with other data, and need to
change certain cells based on a single criteria.

If the cell contains the character : then I need to add 0: to the beginning
of the current string in that cell.

Can anyone help me with a concise bit of code that will search one sheet and
change those selected cells?

Thank you in advance.