Excel 2007 subscript out of range error but runs in 365

[andymalan]

good evening everyone
the following code run fine in excel 365, but throws up a "subscript out of range error" when run in 2007. what must be changed so that it will run in 2007?

 '   Check if "Pos" is found in range A1:A14
       Sheets("Cabinets").Select
    Set rng = Range("A1:A14").Find(What:="Pos", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
            If rng Is Nothing Then
    MsgBox "Position numbers are not present-- select ""with Position"" from Winner, re-print the element list and convert to Excel", vbOK + vbCritical
    If rng Is Nothing Then
    Application.Quit
    End If
    End If

 

[Joe4]

What line of code is highlighted when you hit "Debug"?

I have a feeling it may be this part here:
LookIn:=xlFormulas2

I am not sure that option was available in Excel 2007. Try removing the "2" off the end, and see if that fixes it.

 

[andymalan]

What line of code is highlighted when you hit "Debug"?

I have a feeling it may be this part here:
LookIn:=xlFormulas2

I am not sure that option was available in Excel 2007. Try removing the "2" off the end, and see if that fixes it.

That fixed it Joe4, thank you ever so much. I am going to post another error, and I will be really happy if you could find the time to have a look?
Kind regards
Andy

 

[Joe4]

You are welcome.

All things equal, if you have access to BOTH versions of Excel, it is usually best to write your VBA code on the oldest version you will be using to run it.
If it works on the older version, there is about a 99% chance it will work on the newer version (granted that you have the same drive mappings, libraries selected, etc).
If you write it on the newer version, there is a much higher chance it won't work on the older version, as you may be using newer functionality that was not available on the older version.