Help with match function - defined match value as well as range

[TBooysen]

Hi everyone,

I am having trouble with a match function. I don't know what I am am doing wrong. If I use the match function and manually specify the matchval and matchrng I get the answer that I am looking for, yet when I use VBA I get an error (Runtime error 1004, application-defined or object defined error).
Initially the different variables weren't defined and that also produced the above mentioned error. Will somebody please help me with this issue?

In order to highlight where I used the variables (matchval and matchrng) is used bold letters.

    [B]Dim matchrng As Range[/B]
    'other variables like i, m y and x are also defined
       
    Sheets("Information").Select
    Columns("B:B").Select 'Copy all units over to summary sheet
    Selection.Copy
    Sheets("Summary").Select
    Range("N1").Select
    ActiveSheet.Paste
    Sheets("Information").Select
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Summary").Select
    Range("O1").Select
    ActiveSheet.Paste
    Range("O1").Select
    Selection.End(xlDown).Select
    m = ActiveCell.Row
    
    'some code

    Range("T1").Select
    ActiveCell.Formula = "Module"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-5],FIND(RC[-1],RC[-5],1)+LEN(RC[-1]),3)"
    If m > 2 Then
    Range("T2").Select
        Selection.AutoFill Destination:=Range("T2:T" & m), Type:=xlFillDefault
    End If
    Range("N1").Select
    Selection.End(xlDown).Select
    i = ActiveCell.Row
[B]    Range("S2").Select[/B]
[B]    matchvalue = ActiveCell.Text[/B]
        
    For k = 2 To i
        Range("N" & k).Select
        sht = (ActiveCell.Value & " Sheet")
        If sht = "Jig Sheet" Then
            Sheets(sht).Select
            Range("P1").Select
            Selection.End(xlToRight).Select
            y = ActiveCell.Column
            Cells(1, y + 3).Select
            fractions = ActiveCell.Value
[B]            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))[/B]
        End If
         'Additional if statements removed to truncate the code. Code is similar to this if statement
        
        Sheets("Summary").Select
        Range("U" & k).Select
[B]        ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Match(matchvalue, Sheets(sht).Range(matchrng), 0) 'Error at this line[/B]
        lineval = ActiveCell.Value
        
        Sheets(sht).Select
        For x = 2 To 4 ' since there can be a max of 3 fractions in the spesified range, rows 2 to 4, x should only run between them
            If lineval = 2 Then
                Cells(lineval, y + 12).Select
                Selection.End(xlToRight).Select
                col = ActiveCell.Column - (y + 12)
                If col > 100 Then
                    Cells(lineval, y + 12).Select
                    Selection.Copy
                Else
                    Range(Cells(lineval, y + 12), Cells(k, y + 12 + col)).Select
                    Selection.Copy
                End If
        
            End If
        Next x
        Sheets("Summary").Select
        Range("U" & k).Select 'it is ok to paste over cell that contains match function
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        
        Range("S" & k).Select
        ActiveCell.Offset(1, -1).Activate
        matchvalue = ActiveCell.Value
    Next k

I thank you in advance for your time!

 

[GlennUK]

Why have you got matchrng within the Range keyword?

Match(matchvalue, Sheets(sht).Range(matchrng), 0)

... this is already a range, so shouldn't it be this?

Match(matchvalue, matchrng, 0)

I recommend adding Watches for the matchvalue and matchrng variables, and examining the contents in the Watch window before the error row ( you can expand range objects in the Watch window ), if errors continue to happen.