Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
February 5th, 2013, 03:18 PM
 cacouncill94
Registered User

Join Date: Feb 2013
Posts: 4
Time spent in forums: 1 h 14 m 37 sec
Reputation Power: 0

In my job I deal with employee Id's alot and we have variations of them and sometimes I need to convert them to the other form which is as simple as adding enough zeroes to the beginning of the number until it is 7 digits long. It is not simple when I have hundered or thousands of IDs to convert so i made a macro which goes through the list of IDs and adds the correct number of zeroes until the id has 7 total digits. And it has worked every time I used it up until now and I cannot figure out what is wrong with it. Here is the code, the error is in the "For i = 1 To endrow -1" Like I said, this code has worked every time I have used it up until now. And if it does work it only adds the zeroes to the first cell, but it stops after that which it is not supposed to.

The way it works is, the numbers must be in the A column of the spreadsheet and it checks to see how long the number is then adds the approrpriate number of zeroes if it needs them, then loops to the next. it continues to loop until it reaches a blank cell.

Code:
```Sub AddZeroes()
'Declarations
Dim i As Integer, j As Integer, endrow As Long
'Converts the A column format to Text format
Application.ScreenUpdating = False
Columns("A:A").Select
Selection.NumberFormat = "@"
'finds the bottom most row
endrow = ActiveSheet.Range("A1").End(xlDown).Row
'selects the top cell in column A
ActiveSheet.Range("A1").Select
'loop to move from cell to cell
For i = 1 To endrow - 1
'Moves the cell down 1. Assumes there's a header row so really starts at row 2
ActiveCell.Offset(1, 0).Select
'The Do-While loop keeps adding zeroes to the front of the cell value until it hits a length of 7
Do While Len(ActiveCell.Value) < 7
ActiveCell.Value = "0" & ActiveCell.Value
Loop
Next i
Application.ScreenUpdating = True
End Sub ```

#2
February 5th, 2013, 03:50 PM
 medialint
Type Cast Exception

Join Date: Apr 2004
Location: OAKLAND CA | Adam's Point (Fairyland)
Posts: 14,948
Time spent in forums: 6 Months 2 Weeks 2 Days 5 h 43 m 32 sec
Reputation Power: 8520
Why not just do something like ...

Code:
`Activesheet.Columns("A:A").Numberformat="0000000"`

?

But with your solution you might want to change

Code:
`endrow = ActiveSheet.Range("A1").End(xlDown).Row `

to

Code:
`endrow = Activesheet.cells.specialcells(xlcelltypelastcell).row`
__________________
medialint.com

“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

Last edited by medialint : February 5th, 2013 at 03:54 PM.

#3
February 9th, 2013, 03:42 AM
 GGriggs
Registered User

Join Date: Feb 2013
Posts: 18
Time spent in forums: 4 h 45 m 43 sec
Reputation Power: 0
use excel

easiest way is in excel if you are using VBA

put your number in a cell say A1 and then put

= text(a1,"0000000") in another cell

 Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Add Zeroes Macro Stopped Working