Results 1 to 14 of 14

Code states Match argument not optional

Threaded View

Ochimus Code states Match argument... 05-05-2018, 11:46 AM
Mumps1 Re: Code states Match... 05-05-2018, 11:53 AM
Ochimus Re: Code states Match... 05-05-2018, 12:42 PM
Mumps1 Re: Code states Match... 05-05-2018, 01:00 PM
Ochimus Re: Code states Match... 05-05-2018, 01:28 PM
Mumps1 Re: Code states Match... 05-05-2018, 01:44 PM
Ochimus Re: Code states Match... 05-05-2018, 07:00 PM
bakerman2 Re: Code states Match... 05-06-2018, 01:53 AM
Ochimus Re: Code states Match... 05-06-2018, 08:12 AM
Mumps1 Re: Code states Match... 05-06-2018, 08:42 AM
Marc L Re: Code states Match... 05-06-2018, 08:44 AM
Ochimus Re: Code states Match... 05-06-2018, 02:02 PM
Mumps1 Re: Code states Match... 05-06-2018, 02:04 PM
Marc L Re: Code states Match... 05-07-2018, 08:37 AM
  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365 Version 2501 64-bit
    Posts
    1,465

    Code states Match argument not optional

    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."

    Sub findmatch()
    
        With ActiveSheet
        f = Range("A" & Rows.Count).End(xlUp).Row
        If f < 2 Then f = 2
        
        For x = 2 To f
                
        
        If Error(Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("B" & f), Range("GRADE"), 0), 1) Then
        Range("C" & f) = Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("A" & f), Range("GRADE"), 0)
        Else: Range("C" & f) = Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("B" & f), Range("GRADE", 0))
        
        End If
        
    End Sub
    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
    Attached Files Attached Files
    Last edited by Ochimus; 05-05-2018 at 11:53 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sub argument is not optional
    By Atom in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-28-2017, 05:32 AM
  2. Argument not Optional
    By WesRuss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 09:49 PM
  3. [SOLVED] Optional declared argument is not optional
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-28-2015, 06:59 AM
  4. Argument not optional error is occurring in goal seek automated code.
    By bk1202 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-21-2013, 08:32 AM
  5. 'Argument not optional'
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2011, 03:27 PM
  6. msgbox: Argument not optional
    By tariq1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2011, 06:15 AM
  7. Argument Not Optional
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2011, 12:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1