View Full Version : Search List, Find Sheet, Return Value
dudemansir
12-09-2016, 10:52 AM
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
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
12-09-2016, 12:41 PM
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
dudemansir
12-11-2016, 07:24 PM
Add a ) after B")
Hey SamT. Still getting this response with the syntax. Snapshot attached.
17818
mancubus
12-12-2016, 02:23 AM
insert a dot between sheet object and range object
Sheets(shtName).Range("F:F").Find
Always check the erroring line for typos and missing characters. :devil2:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.