CODEWITHSUNDEEP
excelvba
NoOneImportant
NoOneImportant

Reputation: 15

Compare a cell value to part of a sheet name

I am building a macro that selects data from downloaded .CVS sheets and inserts them into an existing workbook. The downloaded .CVS sheets have names "payment history######-#_date" whose numbers (#) correspond to account numbers which represent the rows in my other workbook. There are more than 60 account numbers.

The idea is to loop through the range containing the account numbers and use a like function to match the account number to the number portion of the worksheet name. It would go something like this:

'variables declared

For Each Row In Accounts 'Accounts references the range of account numbers in the other workbook
    AccNumber = CStr(Rows.Cells(row, 1))
    If sheet.name like "*AccNumber*" then
    ...code continues...
    Next Row

I don't think this is possible using the like function, which compares one string name to a fixed pattern. Is there an alternative/workaround?

Upvotes: 1

Views: 198

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

You may use Instr function to check if the string comparison do return value >0, if not found it will return -1 by using following code, please let me know if any issue as the information is too limited:

For Each Row In Accounts.Cells 'Accounts references the range of account numbers in the other workbook
    AccNumber = CStr(Rows.Value)
    If InStr(Sheet.Name, AccNumber) > 0 Then
    ...code continues...
    Next Row

Upvotes: 2

Related Questions