Search List, Find Sheet, Return Value

[dudemansir]

I need to create a code that will use a column to search the document for a corresponding sheet. I need it to return a value in that corresponding sheet. I provided some images as reference

1779517796

[SamT]

For Each Cel in Range(Range("B1"), Cells(rows.Count, "B").End(xlUp))
ShtName = Cel & Cel.Offset(0, -1)
Cel.Offset(0, 4) = Sheets(shtName)Range("F:F").Find("Total").Offset(0, 1).Value
Next Cel

[dudemansir]

For Each Cel in Range(Range("B1"), Cells(rows.Count, "B").End(xlUp))
ShtName = Cel & Cel.Offset(0, -1)
Cel.Offset(0, 4) = Sheets(shtName)Range("F:F").Find("Total").Offset(0, 1).Value
Next Cel

I put the code in a sub like so. Getting a syntax error on Cel.Offset line, excel is highlightning Range.

Sub FindTotal()
For Each Cel In Range(Range("B1"), Cells(Rows.Count, "B").End(xlUp))
shtName = Cel & Cel.Offset(0, -1)
Cel.Offset(0, 4) = Sheets(shtName)Range("F:F").Find("Total").Offset(0, 1).Value
Next Cel
End Sub

[SamT]

Add a ) after B")

[dudemansir]

Add a ) after B")

Hey SamT. Still getting this response with the syntax. Snapshot attached.

17818

[mancubus]

insert a dot between sheet object and range object

Sheets(shtName).Range("F:F").Find

[SamT]

Always check the erroring line for typos and missing characters. :devil2: