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

Rolsu

New Member
Joined
Jul 16, 2019
Messages
27
Office Version
  1. 2021
Platform
  1. Windows
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:
VBA Code:
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
Code:
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0
@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
VBA Code:
            For f = 0 To ListCount - 1
is pertaining to sheet rows that don't exist, hence summonths doesn't get calculated.

Try this
VBA Code:
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
 
Upvote 0
Solution
@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
VBA Code:
            For f = 0 To ListCount - 1
is pertaining to sheet rows that don't exist, hence summonths doesn't get calculated.

Try this
VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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