Why would my Index formula with wildcard throw me a Subscript out of range error?-CodePudding

With this line, I'm attempting to find the sheet number of the tab that starts with the value of VarCellValue (which has been assigned accurately), with a wildcard meaning anything else after it is ignored: "fromWS = fromWB.Sheets(VarCellValue & "*").Index". Am I going about it the wrong way?

Then below in the If statements, I'd like to use the sheet number to pull data from those tabs.

Sub Update_Chapter_Rankings()

Dim i As Integer
Dim t As Integer
Dim sheetNumber As Integer
Dim sheetName As String
Dim fromWB As Workbook
Set fromWB = Workbooks.Open("S:\Finance\_2021 FINANCIAL REPORTS\National Financials\12-31\CONSOLIDATED MONTHLY FINANCIAL STATEMENT.xlsm")
Dim fromWS As Worksheet
Dim budgWB As Workbook
Set budgWB = Workbooks.Open("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")
Dim toWB As Workbook
Set toWB = Workbooks.Open("I:\Calendar 2022\2022 Account Analysis\2022 Monthly P&L Analyses\Chapter Rankings - 2021\Chapter Rankings 2021.xlsx")
Dim toWS As Worksheet
Dim grossRev As Long
Dim netSurplus As Long
Dim grossSE As Long
Dim grossTC As Long
Dim grossTS As Long
Dim majorGiv As Long
Dim Row As Integer
Dim VarCellValue As String

'Workbooks(fromWB).Open
Workbooks.Open ("I:\Calendar 2022\2022 Account Analysis\2022 Monthly P&L Analyses\Chapter Rankings - 2021\Chapter Rankings 2021.xlsx")

For sheetNumber = ("2") To ("7")

Workbooks.Open ("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")

For i = Range("A2").Value To Range("C2").Value

VarCellValue = Range("A" & i).Value

Row = ActiveSheet.Application.WorksheetFunction.Match(sheetNumber & "*", Range("A:A"), 0)

Workbooks.Open ("S:\Finance\_2021 FINANCIAL REPORTS\National Financials\12-31\CONSOLIDATED MONTHLY FINANCIAL STATEMENT.xlsm")

fromWS = fromWB.Sheets(VarCellValue & "*").Index
sheetName = ActiveSheet.Name
If sheetName = ("Total Gross Revenue") Then
    grossRev = fromWS.Range("C48")
    toWB.Range("H" & Row) = grossRev
ElseIf sheetName = ("Net Surplus (Deficit)") Then
    netSurplus = fromWS.Range("C88")
    toWB.Range("H" & Row) = netSurplus
ElseIf sheetName = ("Special Events Gross") Then
    grossSE = fromWS.Range("C11")
    toWB.Range ("H" & Row)
ElseIf sheetName = ("Team Challenge Gross") Then
    grossTC = fromWS.Range("C14")
    toWB.Range ("H" & Row)
ElseIf sheetName = ("Take Steps Gross") Then
    grossTS = fromWS.Range("C20")
    toWB.Range ("H" & Row)
ElseIf sheetName = ("Major Giving") Then
    majorGiving = fromWS.Range("C30")
    toWB.Range ("H" & Row)
End If


Next i

Next sheetNumber

End Sub

CodePudding user response：

Your below line

fromWS = fromWB.Sheets(VarCellValue & "*").Index

Expects either a 0-based index number or a string to identify the relevant sheet. You provide neither in this case...

Further;

As fromWS is an object it needs to be Set to a WorkSheet object for the rest of your code to work .Index will return an integer not a WorkSheet object.

So it should be either

Set fromWS = fromWB.Sheets(VarCellValue) 'where VarCellValue is an integer

Set fromWS = fromWB.Sheets("Your Sheet Name") 'with a string identifying the name of the sheet

As per @BigBen comments you can loop through the sheets to determine which sheet names comply with your initial VarCellValue & "*" wildcard comparison and when they do, then run your method accordingly.