Adding Zeros in front of numbers in cells using Excel 2003  
Author Message
kalpi





PostPosted: Tue Sep 26 12:11:02 CDT 2006 Top

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

Excel408  
 
 
TomOgilvy





PostPosted: Tue Sep 26 12:11:02 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 Sub fixformatting()
Dim sh as Worksheet, rng as Range
for each sh in Worksheets
set rng = Nothing
On error resume Next
set rng = sh.columns(4).specialCells(xlconstants,xlNumbers)
On Error goto 0
if not rng is nothing then
rng.Numberformat = "0000"
end if
Next
End Sub

Test this on a copy of your workbook.

--
Regards,
Tom Ogilvy



> 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
>
>
 
 
Nigel





PostPosted: Tue Sep 26 12:14:41 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 The following function takes a value as the first parameter, then a length
of the final string with left zero padding, checks that it does not
underflow and returns the padded value in LefdtZeroPad....

Function LeftZeroPad(xValue As Variant, sLength As Integer)
LeftZeroPad = xValue
If Len(xValue) >= sLength Then LeftZeroPad = String(sLength -
Len(xValue), "0") & xValue
End Function

' a test sub for the function above
Sub test()
MsgBox LeftZeroPad(1234, 8)
'returns 00001234
End Sub
--
Cheers
Nigel





> 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
>


 
 
Ronald





PostPosted: Tue Sep 26 12:24:26 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 2 things.

First, for it to work properly, the cells or range would need to be set to
"Text" format instead of "General".

Second, you can use the following code:

If VBA.Len(sh.Cells(Iloop, ColID)) < 4 Then
sh.Cells(Iloop, ColID) = VBA.Left("0000", 4 -
VBA.Len(sh.Cells(Iloop, ColID)))
End If

While this coding would work, if you noticed, I prequalified the Cells
Object as well as the Left Function to avoid the more common sort of
ambiguity that may take place without such prequalification. There may be
reasons for not prequalifying certain objects/variables, but should be rare
in nature. I also have found uses for using things like Select and Activate
methods on Ranges and Worksheets respectively, but again should be rare in
nature as generally, they have more of a tendency of causing problems as you
get more and more into the coding side of things in VBA. Those are a couple
of things I ran into pretty early when I was first learning VBA coding.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000



> 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
>


 
 
TomOgilvy





PostPosted: Tue Sep 26 13:14:03 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 > 'returns 00001234
you must not have used your test sub, because it returns 1234

Think you actually want your test condition in your function to be:

If Len(xValue) < sLength Then LeftZeroPad _
= String(sLength - Len(xValue), "0") & xValue

--
Regards,
Tom Ogilvy



> The following function takes a value as the first parameter, then a length
> of the final string with left zero padding, checks that it does not
> underflow and returns the padded value in LefdtZeroPad....
>
> Function LeftZeroPad(xValue As Variant, sLength As Integer)
> LeftZeroPad = xValue
> If Len(xValue) >= sLength Then LeftZeroPad = String(sLength -
> Len(xValue), "0") & xValue
> End Function
>
> ' a test sub for the function above
> Sub test()
> MsgBox LeftZeroPad(1234, 8)
> 'returns 00001234
> End Sub
> --
> Cheers
> Nigel
>
>
>


> > 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
> >
>
>
>
 
 
TomOgilvy





PostPosted: Tue Sep 26 13:25:02 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 if the cell contains 45

sh.Cells(iloop, colID) = VBA.Left("0000", _
4 - VBA.Len(sh.Cells(iloop, colID)))

changes the cell to "00"

If it just has the value 4, it changes the value to "000"

Think you need to append on the original value.

sh.Cells(iloop, colID) = VBA.Left("0000", _
4 - VBA.Len(sh.Cells(iloop, colID))) & sh.Cells(iloop,colID).Value

However, if the cell had 12345 in it, this would raise an error.
? vba.Left("ABCD",4-5)
raises invalid procedure call or argument.


Generally you would only need to qualify VBA commands with "VBA" if you had
a missing reference in your workbook - which you should fix anyway rather
than bandaid a workaround. Just my opinion of course.

--
Regards,
Tom Ogilvy






> 2 things.
>
> First, for it to work properly, the cells or range would need to be set to
> "Text" format instead of "General".
>
> Second, you can use the following code:
>
> If VBA.Len(sh.Cells(Iloop, ColID)) < 4 Then
> sh.Cells(Iloop, ColID) = VBA.Left("0000", 4 -
> VBA.Len(sh.Cells(Iloop, ColID)))
> End If
>
> While this coding would work, if you noticed, I prequalified the Cells
> Object as well as the Left Function to avoid the more common sort of
> ambiguity that may take place without such prequalification. There may be
> reasons for not prequalifying certain objects/variables, but should be rare
> in nature. I also have found uses for using things like Select and Activate
> methods on Ranges and Worksheets respectively, but again should be rare in
> nature as generally, they have more of a tendency of causing problems as you
> get more and more into the coding side of things in VBA. Those are a couple
> of things I ran into pretty early when I was first learning VBA coding.
>
> --
> Ronald R. Dodge, Jr.
> Production Statistician/Programmer
> Master MOUS 2000
>


> > 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
> >
>
>
>
 
 
Ronald





PostPosted: Tue Sep 26 16:49:05 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 Yes, my mistake earlier, needed to add the other part back to it.

sh.Cells(Iloop, ColID) = VBA.Left("0000", 4 -
> VBA.Len(sh.Cells(Iloop, ColID))) & sh.Cells(Iloop, ColID)


--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000



> 2 things.
>
> First, for it to work properly, the cells or range would need to be set to
> "Text" format instead of "General".
>
> Second, you can use the following code:
>
> If VBA.Len(sh.Cells(Iloop, ColID)) < 4 Then
> sh.Cells(Iloop, ColID) = VBA.Left("0000", 4 -
> VBA.Len(sh.Cells(Iloop, ColID)))
> End If
>
> While this coding would work, if you noticed, I prequalified the Cells
> Object as well as the Left Function to avoid the more common sort of
> ambiguity that may take place without such prequalification. There may be
> reasons for not prequalifying certain objects/variables, but should be
rare
> in nature. I also have found uses for using things like Select and
Activate
> methods on Ranges and Worksheets respectively, but again should be rare in
> nature as generally, they have more of a tendency of causing problems as
you
> get more and more into the coding side of things in VBA. Those are a
couple
> of things I ran into pretty early when I was first learning VBA coding.
>
> --
> Ronald R. Dodge, Jr.
> Production Statistician/Programmer
> Master MOUS 2000
>


> > 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
> >
>
>


 
 
Ronald





PostPosted: Tue Sep 26 16:56:48 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 Well if anything that I have learned from my years of programming, it's best
practice not to leave things to potential ambiguity. When you don't
prequalify your code, as time passes by, your code could become ambiguity by
other additions/modifications, so it's best right from the start not to
leave that chance as a possibility.

As for Left and Right, those are very common ones to get mixed up. Yes, you
can set the priority order of the different references, but that doesn't
resolve every possible ambiguity situation. Yes, the VBA should be the
second one in the list, only to the Excel Object to be the first one in the
list as far as VBA in Excel is concerned. But even then, how do you even
know it's that same order on another computer, if others are using it?

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000



> if the cell contains 45
>
> sh.Cells(iloop, colID) = VBA.Left("0000", _
> 4 - VBA.Len(sh.Cells(iloop, colID)))
>
> changes the cell to "00"
>
> If it just has the value 4, it changes the value to "000"
>
> Think you need to append on the original value.
>
> sh.Cells(iloop, colID) = VBA.Left("0000", _
> 4 - VBA.Len(sh.Cells(iloop, colID))) & sh.Cells(iloop,colID).Value
>
> However, if the cell had 12345 in it, this would raise an error.
> ? vba.Left("ABCD",4-5)
> raises invalid procedure call or argument.
>
>
> Generally you would only need to qualify VBA commands with "VBA" if you
had
> a missing reference in your workbook - which you should fix anyway rather
> than bandaid a workaround. Just my opinion of course.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>

>
> > 2 things.
> >
> > First, for it to work properly, the cells or range would need to be set
to
> > "Text" format instead of "General".
> >
> > Second, you can use the following code:
> >
> > If VBA.Len(sh.Cells(Iloop, ColID)) < 4 Then
> > sh.Cells(Iloop, ColID) = VBA.Left("0000", 4 -
> > VBA.Len(sh.Cells(Iloop, ColID)))
> > End If
> >
> > While this coding would work, if you noticed, I prequalified the Cells
> > Object as well as the Left Function to avoid the more common sort of
> > ambiguity that may take place without such prequalification. There may
be
> > reasons for not prequalifying certain objects/variables, but should be
rare
> > in nature. I also have found uses for using things like Select and
Activate
> > methods on Ranges and Worksheets respectively, but again should be rare
in
> > nature as generally, they have more of a tendency of causing problems as
you
> > get more and more into the coding side of things in VBA. Those are a
couple
> > of things I ran into pretty early when I was first learning VBA coding.
> >
> > --
> > Ronald R. Dodge, Jr.
> > Production Statistician/Programmer
> > Master MOUS 2000
> >


> > > 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
> > >
> >
> >
> >


 
 
Tom





PostPosted: Tue Sep 26 21:05:07 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 the references are unique to the workbook. Moving the file to another
computer doesn't magically add references.

In the thousands of author's whose code I have seen, you are the first I
have seen who recommends routinely prefacing VBA commands with the VBA
qualifier.

And you don't even seem to be hard over on it:

Recent post
Workbooks("Book1.xls").Worksheets("Sheet1").Range(CStr(x) & ":" &
CStr(y)).Rows.Count
instead of
Workbooks("Book1.xls").Worksheets("Sheet1").Range(VBA.CStr(x) & ":" &
VBA.CStr(y)).Rows.Count


--
Regards,
Tom Ogilvy




> Well if anything that I have learned from my years of programming, it's
> best
> practice not to leave things to potential ambiguity. When you don't
> prequalify your code, as time passes by, your code could become ambiguity
> by
> other additions/modifications, so it's best right from the start not to
> leave that chance as a possibility.
>
> As for Left and Right, those are very common ones to get mixed up. Yes,
> you
> can set the priority order of the different references, but that doesn't
> resolve every possible ambiguity situation. Yes, the VBA should be the
> second one in the list, only to the Excel Object to be the first one in
> the
> list as far as VBA in Excel is concerned. But even then, how do you even
> know it's that same order on another computer, if others are using it?
>
> --
> Ronald R. Dodge, Jr.
> Production Statistician/Programmer
> Master MOUS 2000


 
 
Nigel





PostPosted: Wed Sep 27 01:05:54 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 Thanks Tom, No excuses but I added the condition after my tests to prevent
negative String functions! Just shows that never enough testing is a real
issue!

Sorry for confusion

--
Cheers
Nigel





>> 'returns 00001234
> you must not have used your test sub, because it returns 1234
>
> Think you actually want your test condition in your function to be:
>
> If Len(xValue) < sLength Then LeftZeroPad _
> = String(sLength - Len(xValue), "0") & xValue
>
> --
> Regards,
> Tom Ogilvy
>

>
>> The following function takes a value as the first parameter, then a
>> length
>> of the final string with left zero padding, checks that it does not
>> underflow and returns the padded value in LefdtZeroPad....
>>
>> Function LeftZeroPad(xValue As Variant, sLength As Integer)
>> LeftZeroPad = xValue
>> If Len(xValue) >= sLength Then LeftZeroPad = String(sLength -
>> Len(xValue), "0") & xValue
>> End Function
>>
>> ' a test sub for the function above
>> Sub test()
>> MsgBox LeftZeroPad(1234, 8)
>> 'returns 00001234
>> End Sub
>> --
>> Cheers
>> Nigel
>>
>>
>>


>> > 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
>> >
>>
>>
>>


 
 
Ronald





PostPosted: Wed Sep 27 08:21:18 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 True, there are few cases that I haven't been hard fastened to it, which the
data type conversions is one of them. However, certain other ones like
Left, Right, or many of the other string functions as examples, I am hard
fastened to have done that way. There has been so many things that I have
seen done with Left and Right and I have seen weird interactions, thus by
prequalifying those, it most definitely avoids that sort of issue, though
most of the weird ones that I have seen been more so in Access. That
doesn't mean it couldn't happen in any other Office programs. Main reason
why with Access, there's often times codes from different sources that's
interacting with each other that's much more common to happen than with
other Office programs.

Another one that I have seen so often that is within Excel, if you don't
prequalify certain objects such as prequalifying the range object, it will
assume the active parent object, such as the active worksheet at the time it
comes to the range object that isn't prequalified, which may not necessarily
be the correct parent object 100% of the time. For that reason, that's
another situation that I am hard fastened to prequalify, at least all the
way up to the workbook level.

Example:

I have production reports to run at certain times. However, if I didn't
prequalify my objects, but rather relied on the select, activate, and
allowed the code to use active objects as the parents of such child objects,
I would be able to do other things on that system or if I was to do other
things on that system, at least 1 of 2 things, if not both will happen.

1) Focus will be moved from the application that I'm working in to Excel,
which often times is the case with the Activate method.

2) Tasks being done to the wrong parent object such as tasks being done to
range objects on the work worksheet object.


This was one of the first issues that I ran into when I was first learning
VBA, so it didn't take me long to realize that I had to prequalify my
objects to avoid these sorts of issues. That was when I was working with
XL97 on a WIN NT 4.0 system, which to tell the truth, I hated XL97 cause of
the various issues that I ran into with XL97 on the spreadsheet side, which
made Lotus 1-2-3, v2.3 so much easier to work with then XL97, even with SR-2
installed on XL97. However, once XL2K came out, a lot of the issues that I
had in XL97 were resolved in XL2K. It did present some issues, but that's
only cause I had to work between XL2K on my system while others had XL97 on
their system. Why did I have XL2K on my system? MS sent me the Office 2000
disk free of charge as a fix to one of the charting bugs that I faced in
XL97 and didn't really have a viable work around to address the issue in
XL97. Back then, my VBA skill was only like a 3 or 4 on a scale of 1 to 10.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000



> the references are unique to the workbook. Moving the file to another
> computer doesn't magically add references.
>
> In the thousands of author's whose code I have seen, you are the first I
> have seen who recommends routinely prefacing VBA commands with the VBA
> qualifier.
>
> And you don't even seem to be hard over on it:
>
> Recent post
> Workbooks("Book1.xls").Worksheets("Sheet1").Range(CStr(x) & ":" &
> CStr(y)).Rows.Count
> instead of
> Workbooks("Book1.xls").Worksheets("Sheet1").Range(VBA.CStr(x) & ":" &
> VBA.CStr(y)).Rows.Count
>
>
> --
> Regards,
> Tom Ogilvy
>
>


> > Well if anything that I have learned from my years of programming, it's
> > best
> > practice not to leave things to potential ambiguity. When you don't
> > prequalify your code, as time passes by, your code could become
ambiguity
> > by
> > other additions/modifications, so it's best right from the start not to
> > leave that chance as a possibility.
> >
> > As for Left and Right, those are very common ones to get mixed up. Yes,
> > you
> > can set the priority order of the different references, but that doesn't
> > resolve every possible ambiguity situation. Yes, the VBA should be the
> > second one in the list, only to the Excel Object to be the first one in
> > the
> > list as far as VBA in Excel is concerned. But even then, how do you
even
> > know it's that same order on another computer, if others are using it?
> >
> > --
> > Ronald R. Dodge, Jr.
> > Production Statistician/Programmer
> > Master MOUS 2000
>
>


 
 
jfcby





PostPosted: Wed Sep 27 14:09:17 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 Thanks for everyones help jfcby

 
 
TomOgilvy





PostPosted: Wed Sep 27 15:31:03 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 I didn't come out against qualifying things. Just native VBA commands. If
you are happy doing it, then it certainly doesn't hurt. We all have our own
style, but for built in VBA commands, I wouldn't foist that on someone as a
rule to live by.


--
Regards,
Tom Ogilvy





> True, there are few cases that I haven't been hard fastened to it, which the
> data type conversions is one of them. However, certain other ones like
> Left, Right, or many of the other string functions as examples, I am hard
> fastened to have done that way. There has been so many things that I have
> seen done with Left and Right and I have seen weird interactions, thus by
> prequalifying those, it most definitely avoids that sort of issue, though
> most of the weird ones that I have seen been more so in Access. That
> doesn't mean it couldn't happen in any other Office programs. Main reason
> why with Access, there's often times codes from different sources that's
> interacting with each other that's much more common to happen than with
> other Office programs.
>
> Another one that I have seen so often that is within Excel, if you don't
> prequalify certain objects such as prequalifying the range object, it will
> assume the active parent object, such as the active worksheet at the time it
> comes to the range object that isn't prequalified, which may not necessarily
> be the correct parent object 100% of the time. For that reason, that's
> another situation that I am hard fastened to prequalify, at least all the
> way up to the workbook level.
>
> Example:
>
> I have production reports to run at certain times. However, if I didn't
> prequalify my objects, but rather relied on the select, activate, and
> allowed the code to use active objects as the parents of such child objects,
> I would be able to do other things on that system or if I was to do other
> things on that system, at least 1 of 2 things, if not both will happen.
>
> 1) Focus will be moved from the application that I'm working in to Excel,
> which often times is the case with the Activate method.
>
> 2) Tasks being done to the wrong parent object such as tasks being done to
> range objects on the work worksheet object.
>
>
> This was one of the first issues that I ran into when I was first learning
> VBA, so it didn't take me long to realize that I had to prequalify my
> objects to avoid these sorts of issues. That was when I was working with
> XL97 on a WIN NT 4.0 system, which to tell the truth, I hated XL97 cause of
> the various issues that I ran into with XL97 on the spreadsheet side, which
> made Lotus 1-2-3, v2.3 so much easier to work with then XL97, even with SR-2
> installed on XL97. However, once XL2K came out, a lot of the issues that I
> had in XL97 were resolved in XL2K. It did present some issues, but that's
> only cause I had to work between XL2K on my system while others had XL97 on
> their system. Why did I have XL2K on my system? MS sent me the Office 2000
> disk free of charge as a fix to one of the charting bugs that I faced in
> XL97 and didn't really have a viable work around to address the issue in
> XL97. Back then, my VBA skill was only like a 3 or 4 on a scale of 1 to 10.
>
> --
> Ronald R. Dodge, Jr.
> Production Statistician/Programmer
> Master MOUS 2000
>


> > the references are unique to the workbook. Moving the file to another
> > computer doesn't magically add references.
> >
> > In the thousands of author's whose code I have seen, you are the first I
> > have seen who recommends routinely prefacing VBA commands with the VBA
> > qualifier.
> >
> > And you don't even seem to be hard over on it:
> >
> > Recent post
> > Workbooks("Book1.xls").Worksheets("Sheet1").Range(CStr(x) & ":" &
> > CStr(y)).Rows.Count
> > instead of
> > Workbooks("Book1.xls").Worksheets("Sheet1").Range(VBA.CStr(x) & ":" &
> > VBA.CStr(y)).Rows.Count
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >


> > > Well if anything that I have learned from my years of programming, it's
> > > best
> > > practice not to leave things to potential ambiguity. When you don't
> > > prequalify your code, as time passes by, your code could become
> ambiguity
> > > by
> > > other additions/modifications, so it's best right from the start not to
> > > leave that chance as a possibility.
> > >
> > > As for Left and Right, those are very common ones to get mixed up. Yes,
> > > you
> > > can set the priority order of the different references, but that doesn't
> > > resolve every possible ambiguity situation. Yes, the VBA should be the
> > > second one in the list, only to the Excel Object to be the first one in
> > > the
> > > list as far as VBA in Excel is concerned. But even then, how do you
> even
> > > know it's that same order on another computer, if others are using it?
> > >
> > > --
> > > Ronald R. Dodge, Jr.
> > > Production Statistician/Programmer
> > > Master MOUS 2000
> >
> >
>
>
>
 
 
Ronald





PostPosted: Thu Sep 28 08:31:40 CDT 2006 Top

Excel Programming >> Adding Zeros in front of numbers in cells using Excel 2003 I didn't initially do that with VBA methods/properties, but when I ran into
unexpected issues, though mostly took place in Access, it's what caused me
to change my practice on such things. When you bring in other source codes
into your project and it uses such thing like the Left and Right, it will
refer to within the project first and then to the priority list. That's
what caused me to prequalify a lot of the methods/properties.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000



> I didn't come out against qualifying things. Just native VBA commands.
If
> you are happy doing it, then it certainly doesn't hurt. We all have our
own
> style, but for built in VBA commands, I wouldn't foist that on someone as
a
> rule to live by.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>

>
> > True, there are few cases that I haven't been hard fastened to it, which
the
> > data type conversions is one of them. However, certain other ones like
> > Left, Right, or many of the other string functions as examples, I am
hard
> > fastened to have done that way. There has been so many things that I
have
> > seen done with Left and Right and I have seen weird interactions, thus
by
> > prequalifying those, it most definitely avoids that sort of issue,
though
> > most of the weird ones that I have seen been more so in Access. That
> > doesn't mean it couldn't happen in any other Office programs. Main
reason
> > why with Access, there's often times codes from different sources that's
> > interacting with each other that's much more common to happen than with
> > other Office programs.
> >
> > Another one that I have seen so often that is within Excel, if you don't
> > prequalify certain objects such as prequalifying the range object, it
will
> > assume the active parent object, such as the active worksheet at the
time it
> > comes to the range object that isn't prequalified, which may not
necessarily
> > be the correct parent object 100% of the time. For that reason, that's
> > another situation that I am hard fastened to prequalify, at least all
the
> > way up to the workbook level.
> >
> > Example:
> >
> > I have production reports to run at certain times. However, if I didn't
> > prequalify my objects, but rather relied on the select, activate, and
> > allowed the code to use active objects as the parents of such child
objects,
> > I would be able to do other things on that system or if I was to do
other
> > things on that system, at least 1 of 2 things, if not both will happen.
> >
> > 1) Focus will be moved from the application that I'm working in to
Excel,
> > which often times is the case with the Activate method.
> >
> > 2) Tasks being done to the wrong parent object such as tasks being done
to
> > range objects on the work worksheet object.
> >
> >
> > This was one of the first issues that I ran into when I was first
learning
> > VBA, so it didn't take me long to realize that I had to prequalify my
> > objects to avoid these sorts of issues. That was when I was working
with
> > XL97 on a WIN NT 4.0 system, which to tell the truth, I hated XL97 cause
of
> > the various issues that I ran into with XL97 on the spreadsheet side,
which
> > made Lotus 1-2-3, v2.3 so much easier to work with then XL97, even with
SR-2
> > installed on XL97. However, once XL2K came out, a lot of the issues
that I
> > had in XL97 were resolved in XL2K. It did present some issues, but
that's
> > only cause I had to work between XL2K on my system while others had XL97
on
> > their system. Why did I have XL2K on my system? MS sent me the Office
2000
> > disk free of charge as a fix to one of the charting bugs that I faced in
> > XL97 and didn't really have a viable work around to address the issue in
> > XL97. Back then, my VBA skill was only like a 3 or 4 on a scale of 1 to
10.
> >
> > --
> > Ronald R. Dodge, Jr.
> > Production Statistician/Programmer
> > Master MOUS 2000
> >


> > > the references are unique to the workbook. Moving the file to another
> > > computer doesn't magically add references.
> > >
> > > In the thousands of author's whose code I have seen, you are the first
I
> > > have seen who recommends routinely prefacing VBA commands with the VBA
> > > qualifier.
> > >
> > > And you don't even seem to be hard over on it:
> > >
> > > Recent post
> > > Workbooks("Book1.xls").Worksheets("Sheet1").Range(CStr(x) & ":" &
> > > CStr(y)).Rows.Count
> > > instead of
> > > Workbooks("Book1.xls").Worksheets("Sheet1").Range(VBA.CStr(x) & ":" &
> > > VBA.CStr(y)).Rows.Count
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >


> > > > Well if anything that I have learned from my years of programming,
it's
> > > > best
> > > > practice not to leave things to potential ambiguity. When you don't
> > > > prequalify your code, as time passes by, your code could become
> > ambiguity
> > > > by
> > > > other additions/modifications, so it's best right from the start not
to
> > > > leave that chance as a possibility.
> > > >
> > > > As for Left and Right, those are very common ones to get mixed up.
Yes,
> > > > you
> > > > can set the priority order of the different references, but that
doesn't
> > > > resolve every possible ambiguity situation. Yes, the VBA should be
the
> > > > second one in the list, only to the Excel Object to be the first one
in
> > > > the
> > > > list as far as VBA in Excel is concerned. But even then, how do you
> > even
> > > > know it's that same order on another computer, if others are using
it?
> > > >
> > > > --
> > > > Ronald R. Dodge, Jr.
> > > > Production Statistician/Programmer
> > > > Master MOUS 2000
> > >
> > >
> >
> >
> >