Pfthb

I am trying to create a macro that will paste a simple formula to add zeros on the cells that I select. This is what I have but it only works on the defined cells and I am not sure how to change the cell address to work on whatever cells the user selects.

Sub TimeConversion()
 ActiveCell.Formula = "=REPT(0,4-LEN(E8)) & E8" 
End Sub

Can someone tell me what I am doing wrong? I just need to be able to select various cells and run the REPT formula on them.

Thank you!

A basic principle would be to use a worksheet_SelectionChange event but you will probably want to restrict to certain cells as otherwise, any cell you select in the sheet which has the event will get the formula. Thanks to @GMalc for clarifying requirement and offering an amendment.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Application.EnableEvents = False
 Target.NumberFormat = "0000"
 Application.EnableEvents = True
End Sub

You can use Intersect and a specified range to limit to certain cells.

The code goes in the code pane of the sheet you want the formulas to appear in. Not in a standard | class module.

This macro will add four zeros to the select cell/s.

For Each cell In Selection
 cell.NumberFormat = "@"
 cell.Value = WorksheetFunction.Rept("0", 4) & cell
Next

If you're looking out for a Solution in Excel-Formula applied in Excel-VBA, then here it is..

Sub test()
ActiveCell.Formula = "=REPT(0,4-LEN(E" & ActiveCell.Row & "))& E" & ActiveCell.Row & ""
End Sub