CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Find and Replace string within sheet macro using code in another macro

I have a worksheet and worksheet related code called "GL Budget2". The code contains several instances of the string "GL Budget". This string must be renamed "GL Budget2" by using code from within another macro called FindAndReplace. My code (thanks to Pearson):

Sub FindAndReplace()

    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean

    With ThisWorkbook.VBProject.VBComponents("GL Budget2").CodeModule
        SL = 1
        SC = 1
        EL = 99999
        EC = 999
        Found = .Find("GL Budget", SL, SC, EL, EC, True, False, False)

        If Found = True Then
            S = .Lines(SL, 1)
            S = Replace(S, "GL Budget", "GL Budget2")
            .ReplaceLine SL, S
        End If

    End With
End Sub

It crashes with the subscript out of range error on the line

With ThisWorkbook.VBProject.VBComponents("GL Budget2").CodeModule

Any suggestions would be greatly appreciated.

Upvotes: 0

Views: 129

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

GL Budget2 is likely not the code name of your sheet, but the name on the sheet tab.

Try this:

Dim cn as string

cn = ThisWorkbook.Sheets("GL Budget2").CodeName

With ThisWorkbook.VBProject.VBComponents(cn).CodeModule
'...etc

Upvotes: 1

Related Questions