Replace multiple values from date on same worksheet-CodePudding

My Excel 2016 spreadsheet has 3 sheets: Sheet1 (Tab README) Sheet2 (Tab OTHER NOTES) Sheet3 (Tab HYPERLINKS) The spreadsheet may have up 50,000 records. Column B is populated with different file extensions without the dot beginning at B:3. Not every cell in ColB has an extension. The top two rows of my spreadsheet have title information and frozen for viewing when scrolling.

I want to convert these extensions to a descriptive word, e.g., aac, amr, m4p, mp3, pcm to “Audio”, gif, heic, jpg, png, tif to “Image”, etc.

I have a list of extensions on the same spreadsheet: Extensions are J3:J73 with replacement words K3:K73.

When I run the attached VBA, I receive: Run-time error ‘9: Subscript out of range at line Worksheets("Sheet3").Range("B1:B8051").Select

This code runs fine on a single-sheet spreadsheet. I have tried to modify the spreadsheet by making sure there is a extension string in all cells in ColB, and remove the freeze with no change, i.e., same error.

    Sub Ext2FileType ()
        For i = 3 To 73 'Rows with extension/word J3:K73
            Worksheets("Sheet3").Range("B1:B8051").Select '8051 extensions to convert
            Selection.Replace What:=Cells(i,10).Value, Replacement:=Cells(i, 11).Value, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
        Next
        Worksheets("Sheet3").Cells(1, 1).Select
    End Sub

CodePudding user response：

The file tab is named HYPERLINKS. I am under the impression that you would use the "true" name, i.e., Sheet1. Am I incorrect? I will try that way just to eliminate it.

CodePudding user response：

Using the tab name (HYPERLINKS) rather the "true" name, i.e., Sheet1 did the trick.