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. 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 |
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 |
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 |
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 |
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 |
Format for Leading Zeros
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" -- DavidW ------------------------------------------------------------------------ DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630 View this thread: http://www.excelforum.com/showthread...hreadid=534117 |
All times are GMT +1. The time now is 07:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com