Macro help in Excel 2007

[KatParks]

I use Excel 2007, and am the go-to person for most Excel questions in my department, but I've never had to write or execute a macro. I have a list of over 19,500 Member ID's that when imported into an excel sheet, drops the leading zero off of the number. There is no way to control how it is imported, so I thought a macro would work.
I have already formatted the entire column E as "text" only.

I just need to have the macro convert each cell from E2 through E19568 from the value already existing to add 0 in the front.

for instance 12345 would convert to 012345. I thought having it go up to the bar and type '0 in front would work, but apparently not.

This is what it ended when I recorded the macro:
Sub Leading0()
'
' Leading0 Macro
' add the leading 0 to the Member ID
'

'
Range("E2").Select
ActiveCell.FormulaR1C1 = "'0132010"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("E3").Select
End Sub

 

[Trebor76]

Hi KatParks,

Welcome to the forum!!

Why not just format column like this:

"0"#

HTH

Robert

 

[KatParks]

Robert,
You are wonderful, I hadn't even thought of using the custom format to resolve the issue. You have not only helped myself, but my teammate who brought me this question.

Thank you very much!
Kat

 

[Trebor76]

Thanks for letting us know. I'm glad MrExcel was able to provide you with a suitable solution.

Take care

 

[KatParks]

Thank you. Now to find out why I can't see your reply to my private message. It keeps saying that I'm not logged in or have permissions to see that message. I'll post screenshots to the Mods.