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

fullerm

New Member
Joined
Nov 11, 2009
Messages
17
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.

Code:
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 ]
 
Last edited:

fullerm

New Member
Joined
Nov 11, 2009
Messages
17
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.
 
Last edited:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,297
Members
414,440
Latest member
Kim0204

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