piercedgeek
03-19-2007, 05:24 PM
I'm searching for a string, and returning the values under where that string is found. The string is changed often using a Validation dropdown. I was trying to use index/match/vlookup/hlookup and/or offset but couldn't find a way to return what I needed, so I made a Function to do it for me.
But there's a problem with the Function; it runs a little on the slow side on my work computer (something like 5-10 seconds... as soon as I got home and ran it, went down to 1 second heh...). But since this project will be used on my work system, and on other systems at work (which are even worse than what they give me!), I'm still interested in finding a more efficient way to get the job done.
Any ideas or suggestions would be most welcomed.
A note on the amount of time it takes to process, I'm using it in 336 cells.
Here's the function:
Function FindAndOffset(Find_String As String, Find_Sheet As String, _
Offset_Row As Long, Offset_Column As Long)
'Function created by Chris H. on 3/19/2007
'it runs a little bit faster when I just search the range:
FoundRow = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
FoundCol = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column
'code with no range, might be useful for some people, or other situations:
'FoundRow = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
'FoundCol = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column
FindAndOffset = Worksheets(Find_Sheet).Cells(FoundRow + Offset_Row, _
FoundCol + Offset_Column).Value
End Function
Sample:
=FindAndOffset(F1,"8 Week outlook",3,0)
will find the text of cell F1 on tab "8 Week outlook", and return whatever is in the same column, 3 rows down.
I've also attached a sample workbook with sensitive data removed.
Thank you,
Chris
Edited 20-Mar-07 by geekgirlau. Reason: insert line breaks
But there's a problem with the Function; it runs a little on the slow side on my work computer (something like 5-10 seconds... as soon as I got home and ran it, went down to 1 second heh...). But since this project will be used on my work system, and on other systems at work (which are even worse than what they give me!), I'm still interested in finding a more efficient way to get the job done.
Any ideas or suggestions would be most welcomed.
A note on the amount of time it takes to process, I'm using it in 336 cells.
Here's the function:
Function FindAndOffset(Find_String As String, Find_Sheet As String, _
Offset_Row As Long, Offset_Column As Long)
'Function created by Chris H. on 3/19/2007
'it runs a little bit faster when I just search the range:
FoundRow = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
FoundCol = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column
'code with no range, might be useful for some people, or other situations:
'FoundRow = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
'FoundCol = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column
FindAndOffset = Worksheets(Find_Sheet).Cells(FoundRow + Offset_Row, _
FoundCol + Offset_Column).Value
End Function
Sample:
=FindAndOffset(F1,"8 Week outlook",3,0)
will find the text of cell F1 on tab "8 Week outlook", and return whatever is in the same column, 3 rows down.
I've also attached a sample workbook with sensitive data removed.
Thank you,
Chris
Edited 20-Mar-07 by geekgirlau. Reason: insert line breaks