Same edit to multiple cells.

  • Thread starter Thread starter TomAlbert
  • Start date Start date
T

TomAlbert

How do I make the same edit to numerous cells with
different data in each? I need to replace the first
digit in a column of numbers with a zero.
 
Hi
You may use a macro for this. try
Sub change_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = 1 to LastRow
with Cells(RowNdx, "A")
if .value <> "" then
.value = "'0" & mid(.value,2,255)
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

Note: this will convert the numbers to 'Text'
 
You may use a macro for this. try
Sub change_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = 1 to LastRow
with Cells(RowNdx, "A")
if .value <> "" then
.value = "'0" & mid(.value,2,255)
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub
Click to expand...
...

A few things. For generality, it's usually better to make the user select the
cells to be operated upon before running macros as opposed to hardcoding the
range in the macro. Also, the OP likely doesn't want to do this to cells
containing formulas in case such cells are inadvertently selected. Finally,
unlike the MID worksheet function, the 3rd argument to VBA's Mid function is
optional and shouldn't be specified when you want the remainder of the string.


Sub change_rows()
Dim c As Range, f As String

If Not TypeOf Selection Is Range Then Exit Sub

On Error GoTo CleanUp

Application.ScreenUpdating = False

For Each c In Selection
If Not c.HasFormula And c.Formula <> "" Then
c.Formula = "'0" & Mid(c.Formula, 2)
End If
Next c

CleanUp:
Application.ScreenUpdating = True

End Sub
 
Back
Top