Loop trough list by year and month and sum value into textbox

[Rolsu]

Hi all!

Stucked with adjusting a code that sums my hours.
I use two codes to get the value of the sum hours. The first one checks if both cBoxes are set, the second one is called after the check for making the sum of the hours.
The looped list is on the second worksheet. The dates I'd like to loop trough is in col B and the hours in D
Code1:

Private Sub ComboBox3_Change()
    
        With Sheets("Entry_form")
            If ComboBox2.ListIndex < 0 Or ComboBox3.ListIndex < 0 Then Exit Sub
        End With
        
        Call MonthlyHours
        
End Sub

Code 2

Private Sub MonthlyHours()

    Dim myYear As String
    Dim myMonth As String
    Dim f As Integer
    Dim myList As Range
    Dim summonths As Double

        myYear = ComboBox2.Value
        myMonth = ComboBox3.Value

        With Sheets("Data")
            Set myList = Sheets("Data").Range("A1:D1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)
        End With
    
        With myList
            For f = 0 To ListCount - 1
                If Year(.Cells(f, 2)) = myYear And Month(.Cells(f, 2)) = myMonth Then
                    summonths = summonths + .Cells(f, 4).Value
                End If
            Next f
        End With
            
        TextBox7.Value = summonths

End Sub

My problem is I always get 0 in the tBox. I guess the second code does not work properly.
Can someone help me?
Thank you.

 

[I_know_nuffin]

have you tried stepping through the code to see what is happening?

add "stop" here

myMonth = ComboBox3.Value
stop
With Sheets("Data")

what values are in myyear and mymonth

if they are as expected then step through the code using F8

 

[Rolsu]

have you tried stepping through the code to see what is happening?

add "stop" here

myMonth = ComboBox3.Value
stop
With Sheets("Data")

what values are in myyear and mymonth

if they are as expected then step through the code using F8

Hello I_know_nuffin!

Sorry for the late reply.
I've tried to step trough the code but it shows nothing. Just a 0 appeared in tbox7.
The value in the first cbox is 2023, and in the second you can choose the months like 01, 02 until 12. I populate the boxes with the add.item method.

 

[NoSparks]

@Rolsu
myList is a range on a worksheet, worksheets don't have a row 0.
If you were using Option Explicit, ListCount would have been identified as an undeclared variable.
Variables start with no value.
So the line

            For f = 0 To ListCount - 1

is pertaining to sheet rows that don't exist, hence summonths doesn't get calculated.

Try this

Private Sub MonthlyHours()

    Dim myYear As String
    Dim myMonth As String
    Dim f As Integer
    Dim myList As Range
    Dim summonths As Double
    Dim lastrow As Long
    
    myYear = ComboBox2.Value
    myMonth = ComboBox3.Value
    
    With Sheets("Data")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set myList = .Range("A1:D1").Resize(lastrow)
    End With

    With myList
        For f = 2 To lastrow  '<~~~~ assumes headers in row 1
            If Year(.Cells(f, 2)) = myYear And Month(.Cells(f, 2)) = myMonth Then
                summonths = summonths + .Cells(f, 4).Value
            End If
        Next f
    End With
        
    TextBox7.Value = summonths

End Sub

 

[Rolsu]

@Rolsu
myList is a range on a worksheet, worksheets don't have a row 0.
If you were using Option Explicit, ListCount would have been identified as an undeclared variable.
Variables start with no value.
So the line

            For f = 0 To ListCount - 1

is pertaining to sheet rows that don't exist, hence summonths doesn't get calculated.

Try this

Private Sub MonthlyHours()

    Dim myYear As String
    Dim myMonth As String
    Dim f As Integer
    Dim myList As Range
    Dim summonths As Double
    Dim lastrow As Long
   
    myYear = ComboBox2.Value
    myMonth = ComboBox3.Value
   
    With Sheets("Data")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set myList = .Range("A1:D1").Resize(lastrow)
    End With

    With myList
        For f = 2 To lastrow  '<~~~~ assumes headers in row 1
            If Year(.Cells(f, 2)) = myYear And Month(.Cells(f, 2)) = myMonth Then
                summonths = summonths + .Cells(f, 4).Value
            End If
        Next f
    End With
       
    TextBox7.Value = summonths

End Sub

Hello NoSparks!

With your modification the code works perfectly. Thank you!

 

[NoSparks]

You are welcome, glad I could be of assistance