excel macro 1

[jmuscat]

I am wanting to create a macro that will add a zero to the front of certain cell numbers.
I cannot seem to work it out.
Say column A has 856, I need a macro to change it to 0856, but also use the macro on other values in other cells.
Any ideas

 

[waynerenaud]

I am not a programmer, but you could try this.
Add a button to your worksheet and paste the following into the on click event. I am not sire how to show leading zeros in excel but this will put the zero in as text. Hope it helps.

Private Sub CommandButton1_Click()
Dim CellValue
On Error GoTo 1
Set lookfor = Application.InputBox( _
prompt:="Select a cell", Type:=8)
Application.Goto Reference:=lookfor
CellValue = Selection
If CellValue = "" Then
GoTo 1
Else
ActiveCell.Value = "'0" & CellValue
End If
1
End Sub

 

[jmuscat]

waynerenaud
Many thanks for your quick response, but ideally I would like a macro so that I am able to run this on various spreadsheets.
A star for your time

 

[waynerenaud]

You can off course put the code into a macro and it will work exactly the same.
Sub Macro1()
Dim CellValue
On Error GoTo 1
Set lookfor = Application.InputBox( _
prompt:="Select a cell", Type:=8)
Application.Goto Reference:=lookfor
CellValue = Selection
If CellValue = "" Then
GoTo 1
Else
ActiveCell.Value = "'0" & CellValue
End If
1
End Sub

 

[chandlm]

The only problem with this method is that it turns the cell format to a string and not a number. If you need to have the cell still as a number modify the code to


Sub Macro1()
Dim CellValue
On Error GoTo 1
Set lookfor = Application.InputBox( _
prompt:="Select a cell", Type:=8)
Application.Goto Reference:=lookfor
CellValue = Selection
If CellValue = "" Then
GoTo 1
Else
ActiveCell.NumberFormat = "0################"
End If
1
End Sub

HTH

Matt

 

[DvlDawg]

Format the colum to text and it should work.