Help with match function

Hi everybody,

I am trying to use the match function... The code is as follows:

Dim matchrng As Range
'some code here that defines sht as well as y
Sheets("Summary").Select
Range("C" & k).Select
matchvalue = ActiveCell.Value
Sheets(sht).Select
Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
Sheets("Summary").Select
Range("D" & k).Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Match(matchvalue, Sheets(sht).Range(matchrng), 0) 'The problem lays in this line

'some more code

I get an error - Runtime error 1004, application-defined or object defined error.
I think that the issues the range (matchrng) since this is the only variable that I can't see what it is when I hover my mouse over it.

What am I missing or doing wrong?

Thanks for any advice

TL33

What is k equal too?

Hi John,

k is a variable used in a for loop. Sorry for not including the for loop, it completely slipped my mind

TL33

I think we need too see more of your code, including the definitions of your variables too be able to help.

Here is the code:

    Dim matchrng As Range

    'Code that creates the template
    
    'on the summary sheet
    Range("N1").Select  'Column N contains names of different unit operations - jig, drum, cyclone
    Selection.End(xlDown).Select
    i = ActiveCell.Row
    Range("S2").Select 'Column S contains names of different fractions - fine, medium, coarse
    matchvalue = ActiveCell.Value
    
        
   For k = 2 To i
        Range("N" & k).Select
        sht = (ActiveCell.Value & " Sheet")
'if statements to determine on which sheet to collect data
        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
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        If sht = "Cyclone Sheet" Then
            Sheets(sht).Select
            Range("P1").Select
            Selection.End(xlToRight).Select
            y = ActiveCell.Column
            Cells(1, y + 3).Select
            fractions = ActiveCell.Value
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        If sht = "Drum Sheet" Then
            Sheets(sht).Select
            Range("P1").Select
            Selection.End(xlToRight).Select
            y = ActiveCell.Column
            Cells(1, y + 3).Select
            fractions = ActiveCell.Value
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        
        Sheets("Summary").Select
        Range("T" & k).Select
        ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Match(matchvalue, Sheets(sht).Range(matchrng), 0) 'issue here (I think)
        lineval = ActiveCell.Value ' linval will always be between 2 and 4 since a maximum of 3 fractions may be used
        
        Sheets(sht).Select
        For x = 2 To 4 
            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("T" & 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 ' select next fraction
        matchvalue = ActiveCell.Value
    Next k
        
    Sheets("OverallReport").Select

Thank you so much for taking the time to help me

TL33

You need to declare your variables

Sub TL33()
Dim matchrng As Range
'HERE YOU HAVE DEFINED ONLY ONE VARIABLE - matchrng
'YOU ARE USING NUMEROUS OTHERS i, k, x, sht, y    'Code that creates the template
    
    'on the summary sheet
    Range("N1").Select  'Column N contains names of different unit operations - jig, drum, cyclone
    Selection.End(xlDown).Select
    i = ActiveCell.row
    Range("S2").Select 'Column S contains names of different fractions - fine, medium, coarse
    matchvalue = ActiveCell.Value
    
        
   For k = 2 To i
        Range("N" & k).Select
        sht = (ActiveCell.Value & " Sheet")
'if statements to determine on which sheet to collect data
        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
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        If sht = "Cyclone Sheet" Then
            Sheets(sht).Select
            Range("P1").Select
            Selection.End(xlToRight).Select
            y = ActiveCell.column
            Cells(1, y + 3).Select
            fractions = ActiveCell.Value
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        If sht = "Drum Sheet" Then
            Sheets(sht).Select
            Range("P1").Select
            Selection.End(xlToRight).Select
            y = ActiveCell.column
            Cells(1, y + 3).Select
            fractions = ActiveCell.Value
            Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))
        End If
        
        Sheets("Summary").Select
        Range("T" & k).Select
        ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Match(matchvalue, Sheets(sht).Range(matchrng), 0) 'issue here (I think)
        lineval = ActiveCell.Value ' linval will always be between 2 and 4 since a maximum of 3 fractions may be used
        
        Sheets(sht).Select
        For x = 2 To 4
            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("T" & 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 ' select next fraction
        matchvalue = ActiveCell.Value
    Next k
        
    Sheets("OverallReport").Select
End Sub

Hi John,

Even if I declare the variables, the same error occurs. Will you please look at the attached file, it contains my code.
The problem is in the reporting macro.

Thank you so much for you help thus far

TL33