PDA

View Full Version : Solved: How to return a worksheet name based on found data



jason_kelly
12-07-2010, 01:41 PM
Hi There,

I am needing your help to modify the code below to return the "Worksheet Name" that the data was found on via a msgbox using the code below:


Private Sub V3_SEARCH()
Dim rFind As Range, ws As Worksheet
For Each ws In Sheets(Array("Active_Data", "Inactive_Data"))
Set rFind = ws.Columns.Find(What:=Me.h3, LookAt:=xlPart)
If Not rFind Is Nothing Then

MsgBox ws
Me.h1 = rFind.Offset(, -2)
Me.h2 = rFind.Offset(, -1)
Me.h3 = rFind.Offset(, 0)
Me.h4 = rFind.Offset(, 1)
Me.h5 = rFind.Offset(, 2)
Me.h6 = rFind.Offset(, 3)
Me.h7 = rFind.Offset(, 6)
Me.h8 = rFind.Offset(, 7)
Me.h9 = rFind.Offset(, 8)
Me.h10 = rFind.Offset(, 9)
Me.h11 = rFind.Offset(, 10)
Me.h12 = rFind.Offset(, 11)
Me.h13 = rFind.Offset(, 12)

If rFind.Offset(, 4) = "Yes" Then
notify.Value = True
End If

If rFind.Offset(, 5) = "Yes" Then
notify.Value = True
End If

Exit Sub
End If
Next ws

End Sub


Any help with this is greatly appreciated.

Thanks,

Jay

Bob Phillips
12-07-2010, 02:28 PM
Try



MsgBox rFind.Parent.Name

jason_kelly
12-08-2010, 06:44 AM
Awesome!

Thanks so much XLD, it worked like a charm.

This is exactly what I was looking for.

Cheers,

Jay