Results 1 to 5 of 5

Thread: arrays and totalling an element
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default arrays and totalling an element

    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

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: arrays and totalling an element

    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 by AlphaFrog; Sep 21st, 2019 at 05:14 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: arrays and totalling an element

    Quote Originally Posted by AlphaFrog View Post
    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

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: arrays and totalling an element

    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 by zrx1200; Sep 21st, 2019 at 01:10 PM. Reason: addition

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    592
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: arrays and totalling an element

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •