Excel VBA cross-sheet referencing

0
votes

This is my first attempt at using VB in excel so bear with me.

I have a column of names where there are multiple duplicates of each, then in another column is the hours that each person as spent on a particular project. What my function does is goes down the list of names and each time it finds a match with $name it adds the corresponding hours up then returns the total.

Now this works when the table I'm getting the input from is on the same sheet as I'm using the Function, however I want to have the results on a separate sheet from the table. I believe its an issue with how its referencing the cell in line 10 but I'm not sure how to resolve this.

Function Hours(start as Range, finish As Range, name As String) As Double

Hours = 0#
RowStart = start.Row
RowFinish = finish.Row
NameColumn = start.Column
HourColumn = finish.Column

For i = RowStart To RowFinish
    If Cells(i, NameColumn) = name Then Hours = Hours + Cells(i, HourColumn).Value
Next i

End Function
3
excelvba
As a general rule you cannot use a UDF called from the worksheet to manipulate any other cells on the worksheet, nor other worksheets. – David Zemens
But in this case, can't you just call the function from the other sheet (the one where you want the output to appear), passing in the ranges on the Table sheet? I think that would work. – David Zemens

3 Answers

2
votes

As David pointed out, pass in the range from the other sheet and manipulate the data from there. See following UDF.

Function GetTotalHours(EmpNameR As Range, EmpName As String) As Double

    Dim CellR As Range, HoursTotal As Double
    HoursTotal = 0
    For Each CellR In EmpNameR
        If CellR.Value = EmpName Then
            HoursTotal = HoursTotal + CellR.Offset(0, 1).Value
        End If
    Next

    GetTotalHours = HoursTotal

End Function

In action:

Sheet1:

enter image description here

Sheet2:

enter image description here

enter image description here

Hope this helps.

0
votes

you can access any sheet by index of by name, I always prefer by name.

Sheets("my_output_sheet_name").cells(row#, col#).value = total_hours

WHen you do "Cells(i, NameColumn)" it is assuming the current/active sheet. Doing what i said above, allows you to access any sheet regardless of which one is active.

So assuming you want to also list the names in the results sheet, right after the for loop, you could do:

Sheets("results").Cells(resultRow, NameColumn).value = name 
Sheets("results").Cells(resultRow, HourColumn).value = Hours

where obviously you have a sheet called "results" and you will increment "resultsRow" after every name.

0
votes

I realize this does not answer your VBA question, but there is a much easier way to do this within Excel without VBA. Try:

=SUMPRODUCT((NameColumn="Name")*Hours)

The forumula works by testing each name in NameColumn (a named range- you can replace it with the address of your data) with "Name" which results in an array of TRUE and FALSE values. When that array is multiplied by Hours range (also a named range), TRUE values are converted to 1s, and FALSE values are 0s. Then SUMPRODUCT() adds the result.