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
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