List Column Decimal Format

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,113
Code:
Private Sub CommandButton9_Click()
Dim r As Range, vAr1 As Variant, vAr2 As Variant
Dim cnt As Long, i As Long, j As Long, lngCritColumn As Long
Dim arrCrit As Variant
arrCrit = Array("AREA 1", "AREA 2", "AREA 3", "AREA 4", "AREA 5")
ListBox1.Clear
With Worksheets("halls")
Set r = .Range("i1:k" & .Cells(.Rows.Count, "i").End(xlUp).Row)
End With
lngCritColumn = 1
vAr1 = r.Value
cnt = Application.Sum(Application.CountIf(r.Columns(lngCritColumn), arrCrit))
If cnt > 0 Then
ReDim vAr2(1 To cnt, 1 To UBound(vAr1, 2))
cnt = 0
For i = 1 To UBound(vAr1, 1)
If IsNumeric(Application.Match(vAr1(i, lngCritColumn), arrCrit, 0)) Then
cnt = cnt + 1
For j = 1 To UBound(vAr1, 2)
vAr2(cnt, j) = vAr1(i, j)
Next
End If
Next
Me.ListBox1.List = vAr2
End If
With ListBox1
.ColumnCount = 3
.ColumnWidths = "80;80;60"
End With
End Sub


Good Day,
How can we format the column 3 values like ...?
Code:
.NumberFormat = "#,##0.000"
Many Thanks
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
You have to format each value one at a time. Try something like this...
Code:
Private Sub CommandButton9_Click()
Dim r As Range, vAr1 As Variant, vAr2 As Variant
Dim cnt As Long, i As Long, j As Long, lngCritColumn As Long
Dim arrCrit As Variant
arrCrit = Array("AREA 1", "AREA 2", "AREA 3", "AREA 4", "AREA 5")
ListBox1.Clear
With Worksheets("halls")
Set r = .Range("i1:k" & .Cells(.Rows.Count, "i").End(xlUp).Row)
End With
lngCritColumn = 1
vAr1 = r.Value
cnt = Application.Sum(Application.CountIf(r.Columns(lngCritColumn), arrCrit))
If cnt > 0 Then
ReDim vAr2(1 To cnt, 1 To UBound(vAr1, 2))
cnt = 0
For i = 1 To UBound(vAr1, 1)
If IsNumeric(Application.Match(vAr1(i, lngCritColumn), arrCrit, 0)) Then
cnt = cnt + 1
For j = 1 To UBound(vAr1, 2)
vAr2(cnt, j) = vAr1(i, j)
Next
[COLOR="Red"]vAr2(cnt, 3) = Format(vAr2(cnt, 3), "#,##0.000")[/COLOR]
End If
Next
Me.ListBox1.List = vAr2
End If
With ListBox1
.ColumnCount = 3
.ColumnWidths = "80;80;60"
End With
End Sub
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,113
You have to format each value one at a time. Try something like this...
Code:
Private Sub CommandButton9_Click()
Dim r As Range, vAr1 As Variant, vAr2 As Variant
Dim cnt As Long, i As Long, j As Long, lngCritColumn As Long
Dim arrCrit As Variant
arrCrit = Array("AREA 1", "AREA 2", "AREA 3", "AREA 4", "AREA 5")
ListBox1.Clear
With Worksheets("halls")
Set r = .Range("i1:k" & .Cells(.Rows.Count, "i").End(xlUp).Row)
End With
lngCritColumn = 1
vAr1 = r.Value
cnt = Application.Sum(Application.CountIf(r.Columns(lngCritColumn), arrCrit))
If cnt > 0 Then
ReDim vAr2(1 To cnt, 1 To UBound(vAr1, 2))
cnt = 0
For i = 1 To UBound(vAr1, 1)
If IsNumeric(Application.Match(vAr1(i, lngCritColumn), arrCrit, 0)) Then
cnt = cnt + 1
For j = 1 To UBound(vAr1, 2)
vAr2(cnt, j) = vAr1(i, j)
Next
[COLOR=red]vAr2(cnt, 3) = Format(vAr2(cnt, 3), "#,##0.000")[/COLOR]
End If
Next
Me.ListBox1.List = vAr2
End If
With ListBox1
.ColumnCount = 3
.ColumnWidths = "80;80;60"
End With
End Sub


Many Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top