Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Zeros in front of numbers in cells using Excel 2003
Hello,
I have 75 worksheets in my workbook. Each worksheet has 17 columns with various numbers of rows ranging from 1 to 1200. There are data in some rows and numbers in other rows. In column D there are 2 to 4 numbers in each cell like so 75 789 8956 56 22 7234 709 3458 98 I was wondering if the code below can be modifed to add one or two zeros in front of the numbers in the cells that do not have 4 numbers in column D in multiple worksheets. example: 0075 0789 8956 0056 0022 7234 0709 3458 0098 The code: Sub Shorten2() Dim ColID As Integer Dim Iloop As Double Dim NumRows As Double Dim sh As Worksheet Application.ScreenUpdating = False ColID = InputBox("Enter column number you wish to convert.") For Each sh In ActiveWorkbook.Worksheets sh.Select 'Your format code NumRows = Cells(65536, ColID).End(xlUp).Row For Iloop = 1 To NumRows If Len(Cells(Iloop, ColID)) = 2 Then Cells(Iloop, ColID) = "0" & Left(Cells(Iloop, ColID), 4) Else If Len(Cells(Iloop, ColID)) = 3 Then Cells(Iloop, ColID) = "0" & Left(Cells(Iloop, ColID), 4) End If End If Next Iloop Application.ScreenUpdating = True Next sh End Sub I made some modifications to the code to get it to work but it does not add the zero's. Thank you for your help in advance, jfcby |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to display numbers with zeros in front of them, eg 005 | Excel Discussion (Misc queries) | |||
add zeros in front of numbers | Excel Discussion (Misc queries) | |||
How do I keep zeros in front of other numbers (like a zip code)? | Excel Discussion (Misc queries) | |||
insert zeros in front of cells already filled with data. | Excel Discussion (Misc queries) | |||
How do I keep the zeros in front of numbers when i split a cell | Excel Worksheet Functions |