0

Goal: Search B14:C18 for "Global Outperformance Details", if found, put border around B14:C16, if not found, go to next sht (continue loop). The Find statement is working perfectly (i've debug.printed it to test on individual sheets) but I just can't figure out how to properly skip to next sheet if the find is nothing.

Issue: I am getting a '91' error (Object or with variable not defined) on FindGlOutperfDet = which I know occurs when the Find results in nothing. I have tested the code on individual sheets (without the for each loop)and it works put the border in place when the text is found and throws the same error when it is not found.

I have worked through the below pages but still get the error.

how to detect whether VBA excel found something?

IF FIND function doesn't find anything in vba then

Attempt 1:

Sub Findtext()
Dim FindGlOutperfDet As Long
Dim Sht As Worksheet

For Each Sht In Worksheets 
FindGlOutperfDet = Sht.Range("B14:C18").Find(What:="Global Outperformance Details", After:=Range("B18"), LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    
    If Not IsError(FindGlOutperfDet) Then
         Sht.Range("B14:C16").BorderAround ColorIndex:=1, Weight:=xlMedium
    End If

Next Sht
End Sub

Attempt 2:

Sub FindSomeCells123()
Dim FindGlOutperfDet As Range
Dim Sht As Worksheet


For Each Sht In Worksheets
    FindGlOutperfDet = Sht.Range("B14:C18").Find(What:="Global Outperformance Details", After:=Range("B18"), LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    
    If Not FindGlOutperfDet Is Nothing Then
         Sht.Range("B14:C16").BorderAround ColorIndex:=1, Weight:=xlMedium
    End If
Next Sht


End Sub
Jonas
  • 121,568
  • 97
  • 310
  • 388
Ryan S
  • 55
  • 8
  • Hi @BigBen, I have worked through those examples, they don't fully flesh out the issue as it seems something goes awry when it is inside a For Each loop. I think Stack needs more than 2 examples on the topic as it is a common thing to do. – Ryan S Sep 24 '20 at 15:02
  • Did you try and implement the correct approach? Where are you stuck? Maybe [edit] your question with your revised attempt. – BigBen Sep 24 '20 at 15:27
  • Just edited it with an updated approach. I am getting a '91' object variable error due to the find not finding the text on a couple sheets which is what I am trying to deal with in the if not statement. – Ryan S Sep 24 '20 at 15:44
  • Missing a `Set` in your attempt 2. – BigBen Sep 24 '20 at 15:56

0 Answers0