Glaswegian
05-27-2009, 03:29 AM
Hi
I'm using Chip Pearsons' ImportBigTextFiles routine to import a file with around 100K lines. The import works fine and I format some columns, but I'm having a bit of an issue with one particular column.
The data is in this format
750.93
0000000009821.22-
24.50
0000000000018.50-
These values are pound (£) amounts. The numbers with a minus sign to the right are negative pound values. They should simply be shown as
-9821.22
-18.50
However, even when I format the columns (H and I) in the import code, these values do not change. The minus sign seems to be causing the problem.
I'm not sure what the best solution is - a formula, or perhaps something I need to add to my code?
This is the code I've added to the end of Chip's routine
For Each WS In ThisWorkbook.Worksheets
With WS
.Columns.AutoFit
.Columns("N").NumberFormat = "dd/mm/yyyy;@"
.Columns("L").NumberFormat = "0"
.Columns("AP").NumberFormat = "0"
.Columns("AO").NumberFormat = "0"
.Columns("H:I").NumberFormat = "0.00;[Red]0.00"
End With
Next WS
Thanks for any help.
I'm using Chip Pearsons' ImportBigTextFiles routine to import a file with around 100K lines. The import works fine and I format some columns, but I'm having a bit of an issue with one particular column.
The data is in this format
750.93
0000000009821.22-
24.50
0000000000018.50-
These values are pound (£) amounts. The numbers with a minus sign to the right are negative pound values. They should simply be shown as
-9821.22
-18.50
However, even when I format the columns (H and I) in the import code, these values do not change. The minus sign seems to be causing the problem.
I'm not sure what the best solution is - a formula, or perhaps something I need to add to my code?
This is the code I've added to the end of Chip's routine
For Each WS In ThisWorkbook.Worksheets
With WS
.Columns.AutoFit
.Columns("N").NumberFormat = "dd/mm/yyyy;@"
.Columns("L").NumberFormat = "0"
.Columns("AP").NumberFormat = "0"
.Columns("AO").NumberFormat = "0"
.Columns("H:I").NumberFormat = "0.00;[Red]0.00"
End With
Next WS
Thanks for any help.