MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting problem with vb


Posted by Stephen on December 10, 2001 6:04 AM

Hi mrexcel,

I am having a problem correctly formatting a price field when it originates from a vbgrid. It correctly shows the field in the vbgrid and the first couple occurrences in excel, but then it starts displaying the decimal field out to 8 positions instead of 2.
The vbcode look like this: ( I enlarged the code that should format the field)
With XL

.Range("C7:W7").Select
.ActiveWorkbook.Names.Add Name:="Headings1", RefersToR1C1:= _
"=Sheet1!R3C3:R3C23"

Dim Head1
Dim i As Integer
Set Head1 = .Range("Headings1")
i = 0
For i = 1 To 21
Head1(i) = MyHead(i)
Next i

Dim Grid_Rec_Cnt As Integer
Grid_Rec_Cnt = MSHFlexGrid1.Rows
.Range("C7:BR" & Grid_Rec_Cnt + 6).Select

Dim var1 As String
var1 = "sheet1"
.Range("C9:W23").Select
.Range("W9").Activate
.ActiveWorkbook.Names.Add Name:="Load_Data_Range", RefersToR1C1:= _
"=Sheet1!R5C4:R23C23"

.Sheets("Sheet1").Select
.Sheets("Sheet1").Name = "Wash Buy data"

Dim Load_area
.Range("Load_Data_Range").ClearContents
Set Load_area = .Range("Load_Data_Range")
.Selection.NumberFormat = "0;-0;;@"
.Range("A1").Select 'Go to Upper left conner(cell A1)

Dim J As Integer
rsGrid.MoveFirst 'go to the 1st record in the recordset
For i = 1 To rsGrid.RecordCount
For J = 0 To 20
Load_area(i, J) = rsGrid(J)
Next J
rsGrid.MoveNext
Next i

.Application.Goto Reference:="Headings1"
.Selection.Font.Bold = True
End With

With XL
.Cells.Select
.Selection.Columns.AutoFit
.Range("C9").Select
.Selection.NumberFormat = "#,##0.00"
End With

XL.Quit

The excel data shows this:

Call CLIN Sfx ItemCd Description SI Rcpt Qty BPA Cost Price UPC

00320 0167 04370 GRAPEFRUIT 32 CT 35# I 324 AGX99 20.75 35 P
00347 0064 04042 APPLES R/D 88 CT 40# Z 0 AGX05 33.79999924 40 P
00349 0062 06440 ONIONS YELLOW 50# I 100 AGX99 16.75 50 P
00361 0133 04052 TANGERINE FUM. 25# I 2 AGX03 27 25 P
00362 0081 B 06630 POTATO BAKE 70CT/50# Z 100 AGX52 18 50 0 P
00362 0081 C 06630 POTATO BAKE 70CT/50# N 100 AGX52 18 50 0 P
00362 0081 D 06630 POTATO BAKE 70CT/50# Z AGX52 18 50 0 P
01326 0023 A 06098 ONION DRY WH/PLD 25# Z 12 AGX04 12.11999989 25 0 P
01326 0023 B 06098 ONION DRY WH/PLD 25# Z 2 AGX04 12.11999989 25 0 a 0
As you can see, the COST PRICE field intermitently shows invalid formatted data. Is there anything I can do about this? Thanks, Steve Burgio


Posted by Damon Ostrander on December 10, 2001 12:14 PM

Hi Stephen,

I notice that your code only formats cell C9 on the active worksheet. Why not format the whole column? If it is column L (it looks like column L from your listing of the Excel data), the code in your XL With block would be:

.Columns(12).Cells.NumberFormat = "#,##0.00"

Happy computing.

Damon Dim Grid_Rec_Cnt As Integer Grid_Rec_Cnt = MSHFlexGrid1.Rows .Range("C7:BR" & Grid_Rec_Cnt + 6).Select Dim var1 As String var1 = "sheet1" .Range("C9:W23").Select .Range("W9").Activate .ActiveWorkbook.Names.Add Name:="Load_Data_Range", RefersToR1C1:= _ "=Sheet1!R5C4:R23C23" .Sheets("Sheet1").Select .Sheets("Sheet1").Name = "Wash Buy data" .Range("Load_Data_Range").ClearContents Set Load_area = .Range("Load_Data_Range") .Selection.NumberFormat = "0;-0;;@" .Range("A1").Select 'Go to Upper left conner(cell A1) Dim J As Integer rsGrid.MoveFirst 'go to the 1st record in the recordset For i = 1 To rsGrid.RecordCount For J = 0 To 20 Load_area(i, J) = rsGrid(J) Next J rsGrid.MoveNext Next i .Application.Goto Reference:="Headings1" .Selection.Font.Bold = True End With With XL .Cells.Select .Selection.Columns.AutoFit .Range("C9").Select .Selection.NumberFormat = "#,##0.00"