Application.match no longer found in object browser after upgrading to excel 2016

[fullerm]

Longer time user of this site, but rarely have i needed to post a question, as I normally find the anwser, but this one has had me stuck for a couple of weeks (off and on).

A wrote a rather large piece of code, which is basically a database for storing lots of information in mutliple table. Access was probably a better choice in hindsight but Im too far down the track, and I love excel.

This code was written with Excel2007 and I had no errors.

The problem I have is i wrote a macro using the application.match function.

However after upgrading to Excel2016 there are certains function no longer works - they have disappeared.

Further investigation reveals I cannot find the functions in the object browser.

I assume there is some sort of library reference I am missing.

I have the following VBA project References:
* Visual basic for Applications
* Microsoft Excel 16.0 Object Library
* OLE Automation
* Microsoft Forms 2.0 Object Library
* atpvbaen.xls (C:\Program Files\Microsoft Office\Office16\Library\Analysis\atpvbaen.xlam)
* Microsoft Office 16.0 Object Library
* Microsoft Scripting Runtime
* Microsoft HTML Object Library
* Microsoft Internet Controls
* Microsftt Win HTTP Services, version 5.1
* Microsoft ActiveX Data Objects Recordset 2.8 library
* Microsoft ActiveX Data Objects 6.1 Library
* Adobe Acrobat 10.0 Type Library
* Adobe Distiller
* Excel PlugInShell 1.0 Type Library
* EuroTool
* Microsoft Office euro Converter Object Library
* Ref Edit Control
* Solver
* AcessibilityCplAdmin 1.0 Type Library

(I have been activating libraries to try to get these functions back).


The application.match is important as I want to search over two criteria and return the row number of the table.

ElseIf LCase(StationCommodityTable.DataBodyRange(x, 2)) = LCase(StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2)) And _
             StationCommodityTable.DataBodyRange(x, 2) <>       StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2).value Then

In this part of the code I am just data checking records and if names are not Capitalised the same, then I correct it. This removes errors occuring later in the code.


The two functions that are missing from this piece of code are "application.match" and "application.countifs".


I sure there is a simply fix here, but I cannot for the life of me work it out.

Any assistance would be muchly appreciated.

[edited just to make the code line up ]

 

[fullerm]

Thank you for your time, Yongle.

I am considering this thread closed.

I added a fair bit of detail in my later posts in case it can help anyone else with the same problem.

I am aking no further action. I have identified the general casuse of the problem and know how to avoid it or get around if it happens again.

This sub routine takes just over an hour to run. It cross checks about 10 databases with key data and capitalisation where necessary. Two of tables have over about 55,000 records and some with less than 500 records, and I have a 'do events' in each loop.

Obviously the title is wrong on this thread, but I hope the work we did together helps someone.