arrays and totalling an element

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Code:
Dim datalistarray() As String

You declared datalistarray as a String-type.

Code:
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.
Code:
total = total + CLng(datalistarray(a, 3))

That's the quick fix. Alternatively, datalistarray doesn't have to be a string-type array.
 
Last edited:
Upvote 0
Code:
Dim datalistarray() As String

You declared datalistarray as a String-type.

Code:
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.
Code:
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top