8 replies
by glen_mcleod
Dim wkSheet as Worksheet
For Each wkSheet In Workspace.Worksheets
'Search for your job on each sheet
'If Found then
'Get the value of the cell offset by 1
'Retrieve the number into a local variable
Exit for
'End if
Next
This should get you started, it'll loop through all of the sheets in the workspace, I'll leave it to you to search for your specific cell value. The "Exit For" will exit the loop immidiately after finding your search target, so you don't wast time searching after you've already found it.
(This reply box usually doesn't preserve the formating, so there's 1 indent for the lines starting with "Search", "If" and "End If" and 2 indents for the 2 lines between the "If" and "End If")
Glen
by glen_mcleod
Oops, "2 indents for the *3* lines between the "If" and "End If".
Glen
by spp
by spp
by spp
by glen_mcleod
The search function looks like this:
Cells.Find(What:="February 4, 2001", _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Of course, your "What:=" will differ, and you can even pass a parameter in to replace it.
To get the value of an offestting cell, reference it like this:
ActiveCell.Offset(Rows, Columns).Value
Where 'Rows is the number of offset rows you want to reference and 'Columns' is the number of offset columns you want to reference, both can be positive or negative.
So your whole function would look like this:
Public Function WorkSheetsFind(ByVal sWhat As String) As String
Dim wskSheet As Worksheet
Dim rngTarget As Range
Dim sRetVal As String
For Each wskSheet In Workbooks(1).Worksheets
Set rngTarget = Cells.Find(What:=sWhat, After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngTarget Is Nothing Then 'Found it
rngTarget.Activate
sRetVal = ActiveCell.Offset(0, 1).Value
Exit For
End If
Next
WorkSheetsFind = sRetVal
Set rngTarget = Nothing
End Function
I formatted it correctly (indents) in this reply box, but it'll be lost, We'll have to talk to TR about this...
Glen
by spp
by spp
This discussion is closed to new replies.
Trending Software posts
Sort by Trending