VBA line of code no longer recognizing open workbook after name change

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
99
Office Version
  1. 2016
Platform
  1. Windows
Hello. I have this code that matches up data from one workbook to another and pastes in information from one to another. However, one of the workbooks now has a different name format and it will no longer recognize it. Gives me the object variable error. The old name was in the format of DspPickOrder-2020-current date. The new one is named current date-DSP-PickOrder. Here is the code. The line it gives me the error is If InStr(Workbooks(i).Name, "Pickorder"). The code will no longer recognize the "Pickorder name in the new one. Here is the main part of the code. Thanks to anyone willing to help.
VBA Code:
Sub Match()

    
    
    
    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Exit For
    End If
    Next w

    Dim dsp As Workbook         'DISPATCH
    Dim crtx As Workbook        'CORTEX
    Dim rngCTX As Range         'CORTEX RANGE
    Dim sq As Range             'SPARE RANGE
    Dim PO As Worksheet         'PICKORDER SHEET
    Dim i As Long               'ITERATION

    'FIND PICKORDER WORKBOOK & SHEET
    For i = 1 To Workbooks.Count
        If InStr(Workbooks(i).Name, "Pickorder") Then
            Set dsp = Workbooks(i)
        End If
    Next i

    For i = 1 To dsp.Worksheets.Count
        If InStr(Worksheets(i).Name, "Pickorder") Then
            Set PO = dsp.Sheets(Worksheets(i).Name)
        End If
    Next i
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,004
The best thing is to insure your file names have the same format. Why are they so different ?
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
99
Office Version
  1. 2016
Platform
  1. Windows
The best thing is to insure your file names have the same format. Why are they so different ?
I get sent the Pickorder file. It is part of a big macro. Do not want to add a tedious manual step of resaving it.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
The line it gives me the error is If InStr(Workbooks(i).Name, "Pickorder").
Try
Rich (BB code):
If InStr(Workbooks(i).Name, "Pickorder", vbTextCompare) Then
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
99
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try
Rich (BB code):
If InStr(Workbooks(i).Name, "Pickorder", vbTextCompare) Then
Hello. Getting error 13 type mismatch
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
Hello. Getting error 13 type mismatch
Sorry, missed a parameter.
Rich (BB code):
If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) Then
 
Solution

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
99
Office Version
  1. 2016
Platform
  1. Windows
Sorry, missed a parameter.
Rich (BB code):
If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) Then
Rich (BB code):
Thank you so much it works perfectly. Very much appreciated. I’m trying to learn more about VBA. What does this code do that works that my other one did not?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
Thank you so much it works perfectly. Very much appreciated.
Good news .. and you are welcome. :)

What does this code do that works that my other one did not?
It comes down to that final argument: vbTextCompare
That argument is optional and you omitted it. The default value for that is taken from your Option Compare setting and if you have not specifically set it the default for Option Compare is a Binary comparison.
That effectively means that your InStr function is looking for exactly "Pickorder" and would not count "PickOrder", "pickorder", "PICKORDER" etc as a match.
vbTextCompare makes the search case-insensitive. However, when that final argument is used, the first argument (starting position) cannot be omitted which was my mistake in post 4.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,686
Messages
5,637,820
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top