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
Bookmarks