Function to search for string and sum all values in active column in returned row-CodePudding

I am new to excel VBA and tried to setup a personalized function to help me with keeping overview with me teams work capacity. So far unsuccessful :/ A bit more detail:

I have an excel sheet showing the weeks of the year in individual columns and projects ( and names of people) in the rows.

The sheet contains of two parts:

In the upper part I want an overview showing the sum of the days already planned of individual persons in a certain week.
In the lower part I want to give per project the abbreviation of associated people (in column B) and then give the number of days this person is working in this week on this project in the following columns.

I would now like to define a personalised function for the 1st part of my sheet e.g. cell C5 (String PW and Week 1), which can lookup in the range B12:B1000 the string "PW". If the string is found in a certain row ROW1, than this row number should be used to identify the value of days in the column of the week - in this case "C". All found occurrences of the string "PW" should then be used to get the value (days) from all the rows in the column C. Example: The function in C5 should return 6, in D5 should return 3, in C6 should return 5 and so on.

I wrote some code which also worked if I call it in a sub. However, if I call it in the sheet it gives me a value error. Calling the function from the function wizard, it gives me an error when I insert my last input. Which is the ID of the cell from which the function is called. I tried to replace that one with Application.Caller.Address but always got an error :/

Public Function SumName(shortName As String, lookupRange As Range, Caller As Range) As Double

Dim iCol As Integer
Dim Days As Long
Dim nameFind As Range
Dim myCol As String
Dim myRow As Double

Application.Volatile

Days = 0

Cell_called = Split(Caller.Address, "$")
myCol = Cell_called(1)
'Debug.Print myCol

With lookupRange
    Set nameFind = .Find(What:=shortName, _
                            LookAt:=xlWhole, _
                            MatchCase:=False, _
                            SearchFormat:=False)
        firstFind = nameFind.Address
    If Not nameFind Is Nothing Then
        Do
            Cell_found = Split(nameFind.Address, "$")
            myRow = Cell_found(2)
            
            Days = Days   Range(myCol & myRow).Value
            Set nameFind = .FindNext(nameFind)
            
            If nameFind.Address = firstFind Then Exit Do
        Loop While Not nameFind Is Nothing
    End If
End With

SumName = Days

End Function

Now I am a bit lost. Could anyone enlighten me what I did wrong here?

Cheers, Desiree

CodePudding user response：

You dont need VBA for this

for the first part just put a "sumif"

for example, in cell C5 put

=SUMIF(B12:B25,B5,C12:C25)

and so on

this formula will sum the cells in C (12 to 25) column, that have the name in Column B (12 to 25) iqual to the value of B5