[RESOLVED] timevalue additon with array error

[scottlafoy]

Hi,
I am trying to add the time values from part of an array. the array is 0 to 27 but I only want to add parts of it and have it displayed in lanaweek1. I am getting an error on line 1 of the code below, saying type mismatch. The lanatotal(i).text is in "hh:mm" format

Private Sub lanatotal_Change(Index As Integer)
  timetoadd = TimeValue(lanatotal(0).Text) + TimeValue(lanatotal(1).Text) + TimeValue(lanatotal(2).Text)
  lanaweek1 = Format(timetoadd, "hh:mm")
End Sub

thanks in advance for any suggestions

[Siddharth Rout]

You will have to use the same logic that I gave you last time in combination with "On Error Resume Next"

The reason: You are using textbox change event... so even if you type say 3 in that textbox, the event will fire and you will get the type mismatch error....

[LaVolpe]

1. The format is wrong. Should be hh:nn (mins) vs hh:mm (months), no?
2. So the textboxes contain hours & minutes each? And you want to total 3 of them? For example if (0) = 01:05, (1) = 02:20 & (3) = 03:30, the result would be 06:55 ?

[scottlafoy]

ok, I will try that koolsid. LaVolpe that is exactly what I am trying to do, I want (0) and (1) and (2) to add together even if one of them is missing. The format they are in while in the textbox is H:mm eg. 3:15 or 10:45

Thanks,

[LaVolpe]

If I am interpreting correctly, this can tally all the times in the 27 textboxes:

Private Sub lanatotal_Change(Index As Integer)
    Dim subTime As Date, totalHrs As Long, totalMins As Long
    Dim I As Long
    For I = lanatotal.LBound To lanatotal.UBound
        If IsDate(lanatotal(I)) Then
            subTime = CDate(lanatotal(I))
            totalHrs = totalHrs + Hour(subTime)
            totalMins = totalMins + Minute(subTime)
        End If
    Next
    lanaweek1.Text = "Hrs:" & totalHrs + totalMins \ 60 & " Mins: " & totalMins - ((totalMins \ 60) * 60)
End Sub

[scottlafoy]

Thank you, this works, but is there a way to only add up parts of the array, eg (0) to (6) to view in textbox1 and (7) to (13) to view in textbox2?

thank you,

[LaVolpe]

Sure, create a function and call it from the change event

Private Sub TallyTimes(StartIndex As INteger, StopIndex As Integer, destTextBox As VB.TextBox)
   Dim subTime As Date, totalHrs As Long, totalMins As Long
    Dim I As Long
    For I = StartIndex To StopIndex
        If IsDate(lanatotal(I)) Then
            subTime = CDate(lanatotal(I))
            totalHrs = totalHrs + Hour(subTime)
            totalMins = totalMins + Minute(subTime)
        End If
    Next
    destTextBox.Text = "Hrs:" & totalHrs + totalMins \ 60 & " Mins: " & totalMins - ((totalMins \ 60) * 60)
End Sub

Private Sub lanatotal_Change(Index As Integer)
  Select Case Index
  Case 0 To 6
     TallyTimes 0, 6, lanaweek
  Case 7 to 13
     TallyTimes 7, 13, textbox2
  End Select
End Sub

[anhn]

@LaVolpe, This should be shorter and quicker:

Private Sub TallyTimes(StartIndex As Integer, StopIndex As Integer, destTextBox As VB.TextBox)
    Dim totalTime As Date
    Dim I As Long
    
    For I = StartIndex To StopIndex
        If IsDate(lanatotal(I)) Then
            totalTime = totalTime + TimeValue(CDate(lanatotal(I)))
        End If
    Next
    destTextBox.Text = Int(totalTime) * 24 + Hour(totalTime) & ":" & Minute(totalTime)
End Sub

[scottlafoy]

I got this working. Thank you!