How To Match Values from different sheets and get the sum of those values

Good day,

I have a problem regarding the match of values from different sheets.

I have here 3 worksheets, the first one is the Summary, then the two wroksheets is the project.

scenario:
I want to get sum of all the hours that he consume on that day and display it in the Summary Tab.

Here is my macro code, but when i Msgbox at the sheet_hours it loops.

Public Function sum_resource_hours_by_date(name As String, dt As Date) As Double
    ' this is meant to be called in a worksheet formula, but I guess there's no reason it couldn't be called in VBA too
    ' on each of the project worksheets, the resource's name is always in column B, and the date the week begins is always in row 7. If this changes, this function will break.
    Dim ws As Worksheet
    ' r and c are variant so we can store the error if one is returned from Match
    Dim row As Variant
    Dim column As Variant
    Dim name_array As Variant
    Dim date_array As Variant
    Dim sheet_hours As Double
    
    sheet_hours = 0#
    hour_sum = 0#
    Dim u As Double
    
     
    
'// rebuild //----------

    For i = 1 To Sheets.Count
    
        If i <> "" Then
         
'            MsgBox "looking for " & name & " in sheet " & Worksheets(i).name & "."
            name_array = Worksheets(i).Range("B1:B100").Value
            row = Application.Match(name, name_array, 0)
            
                If Not IsError(row) Then
                    
                    date_array = Worksheets(i).Range("A10:CA10")
                    column = Application.Match(CDate(dt), date_array, 0)
            
                        If Not IsError(column) Then
                            sheet_hours = Worksheets(i).Cells(row, column).Value
                            MsgBox sheet_hours
                            hour_sum = hour_sum + sheet_hours
                        End If
    
                End If
        End If
    
    Next i
    
'    sum_resource_hours_by_date = hour_sum
    MsgBox Worksheets(i).Cells(row, column).Value
    
End Function

I appreciate your help. Thanks a lot and comment please