Code states Match argument not optional

[Ochimus]

Col H contains the range of possible "Grades". Col I contains the range of equivalent weighted "Scores"

Col A contains Expected grades. Col B contains Actual grades (where known).

Following Code should work as follows:

If Col B does not match a "Grade" in Col H, then Col C is the "Index" of Col I that "Match" Col A against "Grade" in Col H
Otherwise Col C is the Index/Match of the Grade in Col B

But it throws a "Compile error" that "Match argument is not optional."

Please Login or Register  to view this content.

I know I could use the "formula" approach, but in this case I need a specific VBA solution.

Any pointers, solutions or alternative welcome as ever.

Ochimus

[Mumps1]

Unfortunately the link you posted doesn't work.

[Ochimus]

Sorry. Mumps?

Don't understand the comment?

If you refer to the file I attached, it opens perfectly from the link on both my desktop and a separate Laptop.

Ochimus

[Mumps1]

The first few times I tried the link, it wasn't working. I tried again and I was able to download your file. My apologies but I am a little confused by your explanation. Could you please explain using a few examples from your data in the file and stating what the result would be in column C for each example?

[Ochimus]

Mumps,

There are three examples in the file already, and the expected results are shown in COl E, headed "S/Be".

In row 2, there is an "actual" grade (2) in Col B. So the Code needs to look in Col I for whatever matched "2" in Col H - the answer is 3, as shown in E2.
In row 3, there is no "actual" grade in Col B, so the code needs to look at the value in Col A (2) and find the equivalent for that - the answer is 3, as shown in E3
In row 3, there is an "actual" grade in Col B (D), so the code needs to look in COl I for that equivalent - the answer is 4, as shown in E4

Hope that will hep find the flaw in the VBA code?

Ochimus

[Mumps1]

Try:

Please Login or Register  to view this content.

[Ochimus]

Mumps,

Interesting approach, but sadly doesn't work if B2 has something "non-Grade".

Example: Type "Z" in B2 and run the Macro.

Because B2 is now not blank, your code will look only at the first instruction and seek the matching value in the "SCORE" range in Col I.

As there is no match, C2 remains blank.

That's why I wanted the "IF ERROR INDEX/MATCH" approach, That would trigger the "Error Message" in C2 if no match could be found, and the Code will move to the alternative sequence, and match whatever is in Col A.

Ochimus

[bakerman2]

Here you go.

Please Login or Register  to view this content.

[Ochimus]

bakerman,

Perfect!

I'll mark this as solved, although I'm still unclear why my original ".match" generated the error whilst your structure doesn't?

Ochimus

[Mumps1]

You hadn't mentioned that column B could have something "non-Grade". If you are still interested, this should work.

Please Login or Register  to view this content.

[Marc L]

Hi !

In case of not matching can't work with WorksheetFunction. Never use it but just Application
(example : Application.Match) and check result as error when can't match …

[Ochimus]

Marc,

Thanks for the tip.

Interesting it actually conflicts with MS's own Developer Network, which says to use "Worksheet Function" when writing Visual Basic for Excel, because a Function may have the same name as a Microsoft Excel Function, but work differently. (e.g. "Application.WorksheetFunction.Log" and "Log" will return different values!)

Mumps,
Point taken, and I should have considered that as a factor at the start, given the infinite capacity we have all experienced of End Users doing something completely "left field" and irrational. The results are actually downloaded from an online form, so I could always attempt to persuade the programmers to add DropLists that force the applicants to enter only valid values. (Good Luck with that!!).

Ochimus

[Mumps1]

No problem. I'm glad that it all worked out in the end.

[Marc L]

Interesting it actually conflicts with MS's own Developer Network, which says to use "Worksheet Function" when writing Visual Basic for Excel, because a Function may have the same name as a Microsoft Excel Function, but work differently. (e.g. "Application.WorksheetFunction.Log" and "Log" will return different values!)

No conflict as you misread :

instead of Application.WorksheetFunction.Log just use Application.Log for exactly same result !