Macro Help replacing dropped zeros
I have a complicated problem and I hope I can get help to write a macro
to fix it, I am dealing with the dreaded zero dropping problem, I have tried everything, formatting as text etc, I think my problem is that in the file exported from access, it is dropping the zeros, the numbers appear properly in the cell but up in the formula bar the zeros are missing, I need to cut and paste these numbers into another spreadsheet that gets pulled into another access program. It errors out if the zeros are missing. If I manually add the zeros in the formula bar and put an apostrophe in front everything works fine. I have thousands of these numbers and they all must be 9 charachters. These are national stock numbers, I am thinking the macro that I need will add zeros to equal nine charachters and put an apostrophe in front. I this possible? Some numbers may be missing 1 zero and others may be missing 4 zeros and I need to put them back to equal 9 charachters. I have pretty basic excel skills, anyone willing to help me with this? I do not want to do this manually if I can help it. Thanks, Darrell |
Macro Help replacing dropped zeros
Hi,
Will a worksheet formula do?... =REPT(0,9-LEN(A1)) & A1 I don't think you have to bother with the apostrophe. Ken Johnson |
Macro Help replacing dropped zeros
If I'm wrong about the apostrophe then ="'" & REPT(0,9-LEN(A1)) &A1 Ken Johnson |
Macro Help replacing dropped zeros
Darrell,
Would this suffice: Put this formula in a helper column, copy down and then copy/paste special values =REPT("0",9-LEN(A1))&A1 OR Cells formatted as text. Select you range and run macro. Sub AddZeros() For Each cell In Selection cell.Value = Left("000000000", 9 - Len(cell)) & cell.Value Next End Sub HTH " wrote: I have a complicated problem and I hope I can get help to write a macro to fix it, I am dealing with the dreaded zero dropping problem, I have tried everything, formatting as text etc, I think my problem is that in the file exported from access, it is dropping the zeros, the numbers appear properly in the cell but up in the formula bar the zeros are missing, I need to cut and paste these numbers into another spreadsheet that gets pulled into another access program. It errors out if the zeros are missing. If I manually add the zeros in the formula bar and put an apostrophe in front everything works fine. I have thousands of these numbers and they all must be 9 charachters. These are national stock numbers, I am thinking the macro that I need will add zeros to equal nine charachters and put an apostrophe in front. I this possible? Some numbers may be missing 1 zero and others may be missing 4 zeros and I need to put them back to equal 9 charachters. I have pretty basic excel skills, anyone willing to help me with this? I do not want to do this manually if I can help it. Thanks, Darrell |
Macro Help replacing dropped zeros
Hi,
If it has to be a macro this works if you run it after selecting the range of cells... Public Sub add_zeros() Dim vaData As Variant vaData = Selection Dim I As Long Dim J As Long For I = 1 To UBound(vaData, 1) For J = 1 To UBound(vaData, 2) vaData(I, J) = "'" & _ WorksheetFunction.Rept("0", 9 - Len(vaData(I, J))) _ & vaData(I, J) Next J Next I Selection = vaData End Sub Ken Johnson |
Macro Help replacing dropped zeros
Woohoo, thanks for all the help, had to sleep for a while, Toppers
macro works fine, I cant express my appreciation, this really saved me. Darrell Ken Johnson wrote: Hi, If it has to be a macro this works if you run it after selecting the range of cells... Public Sub add_zeros() Dim vaData As Variant vaData = Selection Dim I As Long Dim J As Long For I = 1 To UBound(vaData, 1) For J = 1 To UBound(vaData, 2) vaData(I, J) = "'" & _ WorksheetFunction.Rept("0", 9 - Len(vaData(I, J))) _ & vaData(I, J) Next J Next I Selection = vaData End Sub Ken Johnson |
All times are GMT +1. The time now is 11:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com