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

"Tom Ogilvy" wrote in message
...
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




"Ronald Dodge" wrote:

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

"jfcby" wrote in message
oups.com...
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