Solved: Listbox Format

[slamet Harto]

Dear Expert,

Can you advise me on how to keep format number in a listbox. Or can you advice me on how to make a bit nice for this listbox.

Pls find the attached for your reference.
Many thanks in advance.
Rgds, Harto

[Bob Phillips]

With ActiveSheet

For R = 0 To echte_laatste_rij - 5 '-10 because data starts at row 6
If Rows(rijteller).Hidden = True Then
rijteller = rijteller + 1
Else
MyList(pos, 0) = .Range("F" & rijteller).Value 'value of cell A in array
MyList(pos, 1) = .Range("G" & rijteller).Value
MyList(pos, 2) = .Range("H" & rijteller).Value
MyList(pos, 3) = .Range("I" & rijteller).Value
MyList(pos, 4) = .Range("J" & rijteller).Value
MyList(pos, 5) = .Range("K" & rijteller).Value
MyList(pos, 6) = Format(.Range("L" & rijteller).Value, "#,###")
MyList(pos, 7) = Format(.Range("M" & rijteller).Value, "#,###")
MyList(pos, 8) = Format(.Range("N" & rijteller).Value, "#,###")
MyList(pos, 9) = Format(.Range("O" & rijteller).Value, "#,###")
MyList(pos, 10) = Format(.Range("P" & rijteller).Value, "#,###")
MyList(pos, 11) = Format(.Range("Q" & rijteller).Value, "#,###")
MyList(pos, 12) = Format(.Range("R" & rijteller).Value, "#,###")
MyList(pos, 13) = Format(.Range("S" & rijteller).Value, "#,###")
MyList(pos, 14) = Format(.Range("T" & rijteller).Value, "#,###")
MyList(pos, 15) = Format(.Range("U" & rijteller).Value, "#,###")
MyList(pos, 16) = Format(.Range("X" & rijteller).Value, "#,###")
MyList(pos, 17) = .Range("Y" & rijteller).Value
MyList(pos, 18) = Application.Text(.Range("Z" & rijteller).Value, "0;(0)")
MyList(pos, 19) = Application.Text(.Range("AA" & rijteller).Value, "#,###;(#,###)")
MyList(pos, 20) = Format(.Range("AB" & rijteller).Value, "0%")

pos = pos + 1 'arrayrow is 1 higher
rijteller = rijteller + 1 'row is one higher
End If
Next R
End With

[Bob Phillips]

Another way.



With ActiveSheet

For R = 0 To echte_laatste_rij - 5 '-10 because data starts at row 6
If Rows(rijteller).Hidden = True Then
rijteller = rijteller + 1
Else
MyList(pos, 0) = .Range("F" & rijteller).Text 'value of cell A in array
MyList(pos, 1) = .Range("G" & rijteller).Text
MyList(pos, 2) = .Range("H" & rijteller).Text
MyList(pos, 3) = .Range("I" & rijteller).Text
MyList(pos, 4) = .Range("J" & rijteller).Text
MyList(pos, 5) = .Range("K" & rijteller).Text
MyList(pos, 6) = .Range("L" & rijteller).Text
MyList(pos, 7) = .Range("M" & rijteller).Text
MyList(pos, 8) = .Range("N" & rijteller).Text
MyList(pos, 9) = .Range("O" & rijteller).Text
MyList(pos, 10) = .Range("P" & rijteller).Text
MyList(pos, 11) = .Range("Q" & rijteller).Text
MyList(pos, 12) = .Range("R" & rijteller).Text
MyList(pos, 13) = .Range("S" & rijteller).Text
MyList(pos, 14) = .Range("T" & rijteller).Text
MyList(pos, 15) = .Range("U" & rijteller).Text
MyList(pos, 16) = .Range("X" & rijteller).Text
MyList(pos, 17) = .Range("Y" & rijteller).Text
MyList(pos, 18) = .Range("Z" & rijteller).Text
MyList(pos, 19) = .Range("AA" & rijteller).Text
MyList(pos, 20) = .Range("AB" & rijteller).Text

pos = pos + 1 'arrayrow is 1 higher
rijteller = rijteller + 1 'row is one higher
End If
Next R
End With

[slamet Harto]

Dear Bob,

Work well as much as my expectation.
Thank you so much.

Best, Harto