CODEWITHSUNDEEP
excelvba
Sean
Sean

Reputation: 71

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

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.

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

Upvotes: 0

Views: 42

Answers (1)

mtholen
mtholen

Reputation: 1661

Your below line

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

Expects either a 1-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

or

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.

Upvotes: 1

Related Questions