+ Reply to Thread
Results 1 to 6 of 6

Format for Leading Zeros

  1. #1
    Registered User
    Join Date
    03-20-2006
    Posts
    38

    Format for Leading Zeros

    I'm importing a report from another workbook. How can I format column A of the destination with leading zeros in the code below? The source is formatted as General. Thanks.

    Please Login or Register  to view this content.

  2. #2
    J.Evans.1970@gmail.com
    Guest

    Re: Format for Leading Zeros

    There's probably a better way, but this would work....


    Dim X As Integer
    Dim wb As Workbook
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\FOReport.xls", True, True)
    With ThisWorkbook.Worksheets("Report")
    .Range("A5:F798") = ""
    .Range("A5:F798").Value =
    wb.Worksheets("Report").Range("C7:H80").Value
    .Range("A5:F798").NumberFormat = "@"
    For X = 5 To 798
    .Range("A" & X & ":A" & X).Value = Format(.Range("A" & X & ":A"
    & X).Value, "00000")
    .Range("B" & X & ":B" & X).Value = Format(.Range("B" & X & ":B"
    & X).Value, "00000")
    .Range("C" & X & ":C" & X).Value = Format(.Range("C" & X & ":C"
    & X).Value, "00000")
    .Range("D" & X & ":D" & X).Value = Format(.Range("D" & X & ":D"
    & X).Value, "00000")
    .Range("E" & X & ":E" & X).Value = Format(.Range("E" & X & ":E"
    & X).Value, "00000")
    .Range("F" & X & ":F" & X).Value = Format(.Range("F" & X & ":F"
    & X).Value, "00000")
    Next
    End With


  3. #3
    AA2e72E
    Guest

    RE: Format for Leading Zeros

    Before the 'End With' statement, try

    .Range("A5:F798").NumberFormat = "000000.00"

    You'd need to adjust "000000.00" for your data ...

    "DavidW" wrote:

    >
    > I'm importing a report from another workbook. How can I format column A
    > of the destination with leading zeros in the code below? The source is
    > formatted as General. Thanks.
    >
    >
    > Code:
    > --------------------
    > Dim wb As Workbook
    > Application.ScreenUpdating = False
    > Set wb = Workbooks.Open("C:\FOReport.xls", True, True)
    > With ThisWorkbook.Worksheets("Report")
    > .Range("A5:F798") = ""
    > .Range("A5:F798").Value = wb.Worksheets("Report").Range("C7:H80").Value
    > End With
    > --------------------
    >
    >
    > --
    > DavidW
    > ------------------------------------------------------------------------
    > DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
    > View this thread: http://www.excelforum.com/showthread...hreadid=534117
    >
    >


  4. #4
    Ivan Raiminius
    Guest

    Re: Format for Leading Zeros

    Hi David,

    add this to your code:

    Dim wb As Workbook
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\FOReport.xls", True, True)
    With ThisWorkbook.Worksheets("Report")
    .Range("A5:F798") = ""
    .Range("A5:F798").Value =
    wb.Worksheets("Report").Range("C7:H80").Value

    .Range("A5:F798").numberformat="000,000"
    'added row of code

    End With

    Change "000,000" to whatever format code you want.

    Regards,

    Ivan


  5. #5
    Tom Ogilvy
    Guest

    RE: Format for Leading Zeros

    Dim wb As Workbook
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\FOReport.xls", True, True)
    With ThisWorkbook.Worksheets("Report")
    .Range("A5:F798") = ""
    .Range("A5:F798").Value = wb.Worksheets("Report").Range("C7:H80").Value
    .Range("A5:F798").Numberformat = "000000"
    End With

    this will show 6 or more digits. If the value in the cell is less than 6
    digits, it will display leading zeros. Adjust to fit your requirements.

    --
    Regards,
    Tom Ogilvy


    "DavidW" wrote:

    >
    > I'm importing a report from another workbook. How can I format column A
    > of the destination with leading zeros in the code below? The source is
    > formatted as General. Thanks.
    >
    >
    > Code:
    > --------------------
    > Dim wb As Workbook
    > Application.ScreenUpdating = False
    > Set wb = Workbooks.Open("C:\FOReport.xls", True, True)
    > With ThisWorkbook.Worksheets("Report")
    > .Range("A5:F798") = ""
    > .Range("A5:F798").Value = wb.Worksheets("Report").Range("C7:H80").Value
    > End With
    > --------------------
    >
    >
    > --
    > DavidW
    > ------------------------------------------------------------------------
    > DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
    > View this thread: http://www.excelforum.com/showthread...hreadid=534117
    >
    >


  6. #6
    Registered User
    Join Date
    03-20-2006
    Posts
    38
    Thanks, everyone.

    I actually only needed to do the A column. I probably did not make that clear. The suggestions were still helpful.

    This works for just one column:

    .Range("A5:A798").numberformat="000000"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1