Changing the format of a column inside a 2-D array

[RawlinsCross]

Greetings,

I have a 2-D array (variable # of rows, 6 columns), I'm looking to go down the second column and convert a number (between 0 and 1) into a time. Trying a few things but unsuccessful. I can index the column (confirmed through debug.Print) but am incorrectly attempting to apply a format to each item in that second column. As an aside, the array subsequently is placed into a listbox (works but time numbers are in 0.00 format)

Attempt

Private Sub SummarizeData()

Dim wSht As Worksheet
Dim rRange As Range
Dim tempArray() As Variant
Dim item As Variant

Set wSht = ThisWorkbook.Worksheets("RawData")
Set rRange = wSht.Range("rngData")

tempArray = rRange.Value

For Each item In Application.Index(tempArray, 0, 2)
     item = Format(item, "h:mm AM/PM")
Next item

Me.lbHistory.List = tempArray

End Sub

 

[Fluff]

You're not changing the array, just the variable "item"
How about

For i = 1 To UBound(tempArray)
     tempArray(i, 2) = Format(tempArray(i, 2), "h:mm AM/PM")
Next i

I would also advise against using VBA keywords (such as item) for the names of variables

 

[RawlinsCross]

That worked and thanks for the tip. Didn't realize that 'item' was a VBA keyword although I don't see it on this list?

Keywords (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[Fluff]

You're welcome & thanks for the feedback.

I tend to use "Keywords" loosely, to refer to any word, phrase, string etc that is used by VBA