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.
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.
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
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
>
>
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
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
>
>
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"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks