Ressource planner for project based working date search

I am working on adding up all values from the same cells in all worksheets in the corosponding cell on the first worksheet. For some reason all sums are 0. By trying it step by step i found the problem in this line:

Set cell = ws.Range("B5 : BZ5").Find(rng.Value)

This worked for me before and I dont understand why it should not work here.

Sub LoopTest()

Dim ws As Worksheet
Dim sum As Double
Dim cell As Range
Dim rng As Range
Dim i As Long
Dim n As Long


i = 2 ' start in Spalte B

    Do While Len(Cells(5, i).Value) > 0
    n = 7 ' start in Reihe 7
    Do While Len(Cells(n, 1).Value) > 0
        Set rng = Worksheets("Ressourcen").Cells(5, i)
        sum = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Ressourcen" Then
                Set cell = ws.Range("B5:BZ5").Find(rng.Value)
                If Not cell Is Nothing Then
                    sum = sum + cell.Offset(n - 5, 0).Value
                End If
            End If
        Next ws

        Worksheets("Ressourcen").Cells(n, i).Value = sum
        n = n + 1
        Loop
        i = i + 1
    Loop
End Sub

if this were to be:

Set cell = ws.Range("B5")

it works

Range.Find

Range.Find can be called by VBA commands, or by the user in Excel.

When you do not specify all the parameters required for a Find, such as the LookIn setting and/or the LookAt setting, it will employ the last settings used. This often results in a different result than expected and can result in no matches at all which can cause errors.

If your VBA stops behaving as it did in previous testing, it's likely that one of these settings has been manually changed by the user/yourself.

Therefore it is highly recommended to include at the very least the LookIn and LookAt parameters when forming your VBA script to ensure the parameters you intended are always used.

eg:

Set cell = ws.Range("B5 : BZ5").Find(What:=Rng.Value, LookIn:=xlValues , LookAt:=xlWhole)

Other parameters can effect the search but can cause issues as often - such as MatchCase (though not in your case, when searching for dates). The full set of parameters are:

Range.Find( What , After , LookIn , LookAt , SearchOrder, SearchDirection , MatchCase _
, MatchByte , SearchFormat )