Find & Replace Text for Hidden Tabs

[mflatt23]

I've got an investing checklist workbook with over 150 companies, each on a separate worksheet. Within each worksheet, there is a question "Is the FCF Yield ≥ 4%?" I'd like to update this text to instead say something different ("Is the FCF Yield ≥ its 10-year median?"). However, from what I'm seeing, I can only make this update using find & replace on unhidden worksheets. I think my computer would significantly slow down or crash if I had to unhide 150+ worksheets to update this question. Is there another way to accomplish this task? Perhaps using VBA Code?

Here is a link to a visual of my spreadsheet (cell B35):

Screenshot-1 hosted at ImgBB

Image Screenshot-1 hosted in ImgBB

ibb.co

Thanks for any help!

 

[igold]

Your question is a little vague as to the specifics of where the text that you want to change is located on the 150+ sheets. However, the generic code below will place the text in the code in every sheet (Cell A1), whether the sheet is hidden or not...

Sub TextinHidden()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long
    
    For i = 1 To wb.Worksheets.Count
        wb.Worksheets.Item(i).Range("A1") = "I am new text"
    Next
    
End Sub

 

[mflatt23]

Good point! So it's on cell B35 for every worksheet, except for about 10 financials that don't have that question. It sounds like I could just apply this code to the entire workbook and then go in and manually update the text for those 10 financials, though? Thank you so much!

 

[igold]

You're welcome. Another logic path to think about is, instead of manually updating those sheets, include code that will skip those sheets in the For/Next loop. Worse case, is you could hard code the names of the sheets right in the code. Best case, is that the names or some facet of the sheets share a commonality that the code could look for to skip them during the loop.

 

[mflatt23]

I don't mind doing the manual sheet names. Would it be something like:

If sh.Name <> "IgnoreThisSheet" Then

Been a little while since I've done the skip logic, feel like I might be missing something...lol

Really appreciate it!

 

[igold]

How about something like this. Put the sheet names you want to skip in this statement

Case Is = "Sheet1", "Sheet2", "Sheet3"

Sub TextinHidden()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long

        For i = 1 To wb.Worksheets.Count
            Select Case wb.Worksheets(i).Name
                Case Is = "Sheet1", "Sheet2", "Sheet3"
                Case Else
                    wb.Worksheets.Item(i).Range("B35") = "I am new text"
            End Select
        Next
    
End Sub

 

[mflatt23]

That worked! I won't forget this thank you

 

[igold]

I was happy to help. Thanks for the feedback!

 

[mflatt23]

@igold sorry last question! I've gotten it to work and the new text now looks like:

wb.Worksheets.Item(i).Range("B35") = "Is FCF Yield > the U.S. 10-year Treasury Yield or its 10-year median?"

If I want to bold FCF Yield how would I go about adding that in there? I've looked on stackoverflow, but haven't found any relatively easy answers.

 

[igold]

How about this:

Sub TextinHidden()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long, Sp As Long
    Dim rng As Range

        For i = 1 To wb.Worksheets.Count
            Select Case wb.Worksheets(i).Name
                Case Is = "Sheet1", "Sheet2", "Sheet3"
                Case Else
                    wb.Worksheets.Item(i).Range("B35") = "Is FCF Yield > the U.S. 10-year Treasury Yield or its 10-year median?"
                    Set rng = wb.Worksheets(i).Range("B35")
                    Sp = InStr(1, rng.Value, "FCF Yield")
                    rng.Characters(Start:=Sp, Length:=9).Font.Bold = True
            End Select
        Next
    
End Sub