Stuck using Index match with VBA...

[cillyRabbit]

Hello everyone,
Programming was a trait I stopped polishing 10+ years ago. VBA is entirely new to me.
We needed to put together a CMMS system and I couldn't find one out there that I really liked so I set out to build my own. I do enjoy the struggles but I have reached the point where I need some help.
CMMS is just tracking maintenance. So I have a table that has the following columns (MaintenanceID, Date, AssetID, AssetName, AssetHours (Heavy equipment), Maintenance Type, etc.)
I am able to use Application.WorksheetFunction.MaxIfs to determine the date that the last maintenance that was done for an asset.
I am able to generate the assetID.
However with the assetID and the last date of service held in separate variables, I can not for the life of me figure out how to pull the "AssetHours" from the last service.
I have tried Index..Match but I am at a loss and keep running in syntax circles.
Would anyone be interested in looking at the project?
Thank you,
-Kevin

What I have so far is pretty neat, at a glance our mechanic will be able to see in a color coded fashion what assets need service, and how much life (as a percentage) is left before they need service.

 

[Michael M]

You can either post a sample of data using Xl2BB ( see my tag) along with the codes you are using
OR
You could upload to a hosting site, Dropbox for instance, and then post the link to that file back here. I'm sure someone will be happy to take a look at it.

 

[cillyRabbit]

I would love to collaborate with someone and share the project.
Maybe before I ask too much of someone I will post the line I am having the most difficulty with.
Please see the following. I am looking to retrieve the hour meter reading (Column 2) for that row which matches a defined "assetID"(ColumnG) and "PM_date" (ColumnF)

PM_units = WorksheetFunction.Index(Sheets("Maintenance-Records").Range("E13:N100"), WorksheetFunction.Match(1, (assetID = Sheets("Maintenance-Records").Range("G13:G10000")) * (PM_date = Sheets("Maintenance-Records").Range("F13:F10000")), 0),2)

 

[Michael M]

For starters if the index / Match is on the active sheet, you won't need a sheet reference in the formula

PM_units = WorksheetFunction.Index(Range("E13:N100"), Match(1, (assetID = Range("G13:G10000")) * (PM_date =Range("F13:F10000")), 0),2)

Bit hard to delve further into the formula without seeing the table and required result.
I believe this part of the formula needs to be a cell reference....

Match(1

 

[cillyRabbit]

Please see the file below via dropbox.
There are 4 worksheets within the workbook.
"Maintenance Records": Contains a line for each maintenance record.
"Quick Overview": Is the main interaction screen and is the one that I am struggling to make perform.
"Lists": Is a list of all the assets/equipment and additional information on each piece
"Variables": Is used gently as I was learning VBA.
The macro that contains the problem is [updateReviewPage] and is activated by clicking the [Refresh Health] button.

Problem: Within [updateReviewPage] macro I am struggling to pull the correct "Asset Hours" from the most recent "Maintenance Records" in which the correct tier of Maintenance was performed and assign it to the PM_units variable.
-There are 3 levels of maintenance that can be performed (1,2,3) and this is seen on the "Lists" worksheet.
-there is a loop that loops through 3 times and updates the color/value of each cell on the "Quick Overview" page.
-Then if the asset is due for maintenance based on hours or time since last service (days/months) it changes color and displays the "Health" of the equipment as a percentage.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Michael M]

Downloaded the file, but I get a "The file is corrupted and can't be opened" error
Is there something in the file that is not compatible with version 2016
Also, have you tried using the INDEX / MATCH function on the worksheet to see if it works, BEFORE putting it into a VBA format ??

 

[cillyRabbit]

I am running a 32 bit version of 2019 excel. I did find today it is necessary for it to be a 32 bit version to allow the calendar selector in a couple of the forms that are used.
I will try to INDEX/MATCH function in the worksheet first and get back to you.
Thank you,
-Kevin

 

[cillyRabbit]

I was able to put a little effort into this this evening.
I am able to make it work directly in the worksheet using the following:
{=INDEX('Maintenance-Records'!E13:E10000,MATCH(1,(V3='Maintenance-Records'!G13:G10000)*(V4='Maintenance-Records'!F13:F10000),0))}

The closest I can get in the macro is the following:
PM_units = WorksheetFunction.Index(Sheets("Maintenance-Records").Range("E13:E10000"), WorksheetFunction.Match(1, ("0002" = Sheets("Maintenance-Records").Range("G13:G10000") * ("01/01/2021" = Sheets("Maintenance-Records").Range("F13:F10000"))), 0))

However now when I try to run the macro I receive the following "Run-time error '13': Type mismatch.

Thoughts?

Thank you in advance.

 

[cillyRabbit]

I was able to put a little effort into this this evening.
I am able to make it work directly in the worksheet using the following:
{=INDEX('Maintenance-Records'!E13:E10000,MATCH(1,(V3='Maintenance-Records'!G13:G10000)*(V4='Maintenance-Records'!F13:F10000),0))}

The closest I can get in the macro is the following:
PM_units = WorksheetFunction.Index(Sheets("Maintenance-Records").Range("E13:E10000"), WorksheetFunction.Match(1, ("0002" = Sheets("Maintenance-Records").Range("G13:G10000") * ("01/01/2021" = Sheets("Maintenance-Records").Range("F13:F10000"))), 0))

However now when I try to run the macro I receive the following "Run-time error '13': Type mismatch.

Thoughts?

Thank you in advance.

In the worksheet example V3=0002 and V4=01/01/2021

 

[Michael M]

Don't have Excel ATM, try

PM_units = Application.WorksheetFunction.Index(Sheets("Maintenance-Records").Range("E13:E10000"), Match(V3 = Sheets("Maintenance-Records").Range("G13:G10000") * (V4 = Sheets("Maintenance-Records").Range("F13:F10000"))), 0)

You could also assign the formula to a cell, ie

[A1].formula = "{=INDEX('Maintenance-Records'!E13:E10000,MATCH(1,(V3='Maintenance-Records'!G13:G10000)*(V4='Maintenance-Records'!F13:F10000),0))}"

followed by

PM_Units=[A1].value