arrays and totalling an element

[zrx1200]

here is a snip it of code for totalling time numbers of items pulled from a listbox into an array, then another array holding data is search for a match then the time number pulled from dataarraylist postion 3 column.

msgbox total should show say 0:33 BUT shows 1.111111111111111E-021.5972222222222e-02 its like its appending not totalling ?

Dim total As Variant
Dim datalistarray() As String
Dim a As Long, b As Long
Dim sh As Worksheet
Dim i As Integer
Dim NameArray() As String
Set sh = ThisWorkbook.Sheets("Data")

'cMyListbox is listbox used, setup as public varibles when driver selects name.
With UserForm1.cMyListbox
ReDim NameArray(.ListCount)

'Load the listbox values into the array
For i = 0 To .ListCount - 1
NameArray(i) = .List(i)
Next i

'print the array values to the debug window
For i = 0 To UBound(NameArray) - 1
Debug.Print i, NameArray(i)
MsgBox NameArray(i)

ReDim Preserve datalistarray(1 To sh.Range("A" & Rows.count).End(xlUp).row, 1 To 4)

For a = 1 To sh.Range("A" & Rows.count).End(xlUp).row

For b = 1 To 4
datalistarray(a, b) = sh.Cells(a, b)
Next b
Next a

For a = 2 To UBound(datalistarray)
If datalistarray(a, 1) = NameArray(i) Then

'this is where i hope to get the number and alpha that matches namearray(i)in dataarraylist
'will need to "move over two columns total, one column for num and one more colum for alpha in the datalistarray
'CODE HERE ?

MsgBox "Yes, it does match"

' MsgBox Format(datalistarray(a, 3), "h:mm")
' MsgBox datalistarray(a, 4)
' addthis = datalistarray(a, 3)
total = total + datalistarray(a, 3) '<=============== not totaling but appending basically?
MsgBox total


Exit For
MsgBox "No match found !"
End If
Next a

Next i

End With

 

[AlphaFrog]

Dim datalistarray() As String

You declared datalistarray as a String-type.

total = total + datalistarray(a, 3)

When you use String + String, it concatenates strings. Not sums them.

Try this to convert the string into a numeric value.

total = total + CLng(datalistarray(a, 3))

That's the quick fix. Alternatively, datalistarray doesn't have to be a string-type array.

 

[zrx1200]

Dim datalistarray() As String

You declared datalistarray as a String-type.

total = total + datalistarray(a, 3)

When you use String + String, it concatenates strings. Not sums them.

Try this to convert the string into a numeric value.

total = total + CLng(datalistarray(a, 3))

That's the quick fix. Alternatively, datalistarray doesn't have to be a string-type array.

Ok tried and get a zero. Also put data type to variant using the clng and zero

 

[zrx1200]

Ok, see bold text. This is what I changed and it now works? Is it proper I don't know, any thoughts?

msgbox total time now 0:45 as example, which is what format I'd expect to see.

Dim total As Variant
Dim datalistarray() As Variant
Dim a As Long, b As Long
Dim sh As Worksheet
Dim i As Integer
Dim NameArray() As String
Set sh = ThisWorkbook.Sheets("Data")

'cMyListbox is listbox used, setup as public varibles when driver selects name.
With UserForm1.cMyListbox
ReDim NameArray(.ListCount)

'Load the listbox values into the array
For i = 0 To .ListCount - 1
NameArray(i) = .List(i)
Next i

'print the array values to the debug window
For i = 0 To UBound(NameArray) - 1
Debug.Print i, NameArray(i)
MsgBox NameArray(i)

ReDim Preserve datalistarray(1 To sh.Range("A" & Rows.count).End(xlUp).row, 1 To 4)

For a = 1 To sh.Range("A" & Rows.count).End(xlUp).row

For b = 1 To 4
datalistarray(a, b) = sh.Cells(a, b)
Next b
Next a

For a = 2 To UBound(datalistarray)
Dim LBName As String
LBName = NameArray(i)
If datalistarray(a, 1) = LBName Then

MsgBox "Yes, it does match"

total = total + datalistarray(a, 3) '<=============== not totaling but appending basically?
MsgBox Format(total, "h:mm")

Exit For
MsgBox "No match found !"
End If
Next a

Next i

End With

 

[zrx1200]

Ok, following this thread I have working array frame work for totalling.

I now would like to take listbox contents NameArray and pull the contents of column 3 from the datalistarray if it matches the namearray, which would go into another array which would have the matching column 3 figures. This array would allow me to find the max of the elements in this array for later use.

Please be patient folks, as this is hard enough to understand let alone explain things.