Results 1 to 4 of 4

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

Threaded View

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    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
    Attached Files Attached Files
    Last edited by lofthi; 05-31-2013 at 12:56 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1