Adding Sheet and row address to MsgBox

[DThib]

Well this is probably an easy answer but all I have tried gives me an error.
This is the part that works as intended but I cannot derive the sheet name where found and the row it was found on that sheet.

            ElseIf rng Is Nothing Then
              Set ra = ActiveSheet.Cells.Find(What:=SPR_W, LookIn:=xlFormulas, LookAt:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                MsgBox "The information is found in " & vbCrLf & " worksheet:" _
                & vbCrLf & [COLOR=#b22222][B]Sheet.name[/B][/COLOR], vbInformation + vbOKOnly, "Wrong worksheet"

DThib

 

[Norie]

Where are you setting the value of Sheet?

 

[DThib]

Now that you mention it, would it be better to leave this a variable as well?

If the data is not found in the worksheet queried initially ("Sheet 1"), find it in 1 of 2 other worksheets ("Sheet 2", "Sheet 3") where it may live.

That is the reason I would like to show in the MsgBox the sheet and the row.

I have tested with data I know is not in ("Sheet 1") and it runs through correctly, I just can't draw the information.

 

[Norie]

What do you mean 'leave it as a variable'?

Isn't it already a variable?

 

[Fluff]

How about

   MsgBox "The information is found in " & vbCrLf & " worksheet:" _
         & vbCrLf & ra.Parent.Name & " row " & ra.Row, vbInformation + vbOKOnly, "Wrong worksheet"

 

[DThib]

Sorry about the confusion.
The Initial sheet that is running the UserForm this is tied to is where the data should be found, ("Sheet 1").
If that returns nothing when searched, look in the other 2 sheets and tell me where it is found.
The Not Nothing statement (initial) works great the ElseIf is giving me trouble.

Variable:
I meant maybe I should try to not define the sheet in this part of the code (Is Nothing) to have the find statement look at the other 2 sheets.

 

[Norie]

Can you post the rest of the code?

 

[DThib]

Private Sub GOCB_Click()


  Dim WoWg As Worksheet
  Dim WGo As String
  Dim WGoW, rng, ra As Range
  Dim wrng


  If SPR_IDTB <> "" Then
     SPR_W = Work_FRM.SPR_IDTB.Value
    With Sheets("Workable").Range("B:B")
            Set rng = .Find(What:=SPR_IDTB, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not rng Is Nothing Then
                Set WoWg = Sheets("Workable")
                 WoWg.Activate
                Set WGoW = WoWg.Columns("B").Find(SPR_W, LookIn:=xlValues, LookAt:=xlWhole)
                'Determine WorkingRow
                 WorkingRow = WorksheetFunction.CountA(Range("A:A"))
                  Wprod_TB.Value = WoWg.Cells(WGoW.Row, 6).Value
                  Wdaysw_TB.Value = WoWg.Cells(WGoW.Row, 12).Value
                  WSum_TB.Value = WoWg.Cells(WGoW.Row, 7).Value
                  Wsever_TB.Value = WoWg.Cells(WGoW.Row, 8).Value
                  Wassign_TB.Value = WoWg.Cells(WGoW.Row, 3).Value
                  Wserialn_TB.Value = WoWg.Cells(WGoW.Row, 9).Value
                  Wdayso_TB.Value = WoWg.Cells(WGoW.Row, 13).Value
            ElseIf rng Is Nothing Then
              Set ra = ActiveSheet.Cells.Find(What:=SPR_W, LookIn:=xlFormulas, LookAt:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                MsgBox "SPR ID " & SPR_W & " is found in " & vbCrLf & " worksheet:" _
                & vbCrLf & "Boo", vbInformation + vbOKOnly, "Wrong worksheet" 'ActiveSheet.Address &
            End If
    End With
  Else
     EmptyBox_W
     MsgBox "Add Number", vbCritical + vbOKOnly, "Here we go..."
  End If
  Call Wister


End Sub

 

[Fluff]

How about

   ElseIf Rng Is Nothing Then
      For Each Ws In Worksheets
         If Not Ws.Name = "Workable" Then
            Set ra = Ws.Cells.Find(What:=SPR_W, lookIn:=xlFormulas, LookAt:=xlPart, _
               SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not ra Is Nothing Then
               MsgBox "SPR ID " & SPR_W & " is found in " & vbCrLf & " worksheet: " & ra.Parent.Name _
                  & vbCrLf & "Row " & ra.Row, vbInformation + vbOKOnly, "Wrong worksheet" 'ActiveSheet.Address &
               Exit For
            End If
         End If
      Next Ws
   End If

 

[DThib]

Once again, you are the Fluffiest!

Thanks

DThib