List Column Decimal Format

[asyamonique]

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 ...?

.NumberFormat = "#,##0.000"

Many Thanks

 

[AlphaFrog]

You have to format each value one at a time. Try something like this...

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]

You have to format each value one at a time. Try something like this...

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