Difficulty with the worksheetfunction.match property in VBA

Question

I keep getting the error "Run-time error '91': "Object Variable or With block variable not set" when my macro gets to the line

matchrange = Workbooks("tracker test").Sheets(start_sheet).Range("F" & h).Value

where I am trying to define the PLnumber that will be compared to the PL_compare_list named range. If I try to not define that variable and instead just put the reference directly into my match function on the line below I instead get the error "Run-time error '1004': Unable to get the Match property of the WorksheetFunction class"

What I am trying to do is have this code look at column H on start_sheet to see if it has data yet. then, if it does not, compare the PL numbers on start_sheet in column F to the PL numbers on "Calculation Sheet" in column B to find a row and then open the corresponding file name that is in column A in that row. Thoughts?

Here is my code in its entirety but I think the most relevant bits will be close to the bottom:

Option Explicit


Sub GetFileNames()

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=REPLACE(CELL(""filename""),FIND(""["",CELL(""filename"")),LEN(CELL(""filename"")),MID(CELL(""filename""),FIND(""]"",CELL(""filename""),1)+1,255))&""_samples shipment PO_PL_Invoice_ attachment\""&TRIM(MID(CELL(""filename""),FIND(""]"",CELL(""filename""),1)+1,255))&""_PL\"""
Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=left(RC[-1],len(RC[-1])-10)"

Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Dim directory As String
    directory = Range("B1").Value


Dim start_sheet As String
start_sheet = ActiveSheet.Name
Sheets("Calculation Sheet").Activate

Range("D1") = Sheets(start_sheet).Range("A1").Value

Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents

ActiveSheet.Cells(1, 1).Select

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = directory
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With

Dim i As Integer
Dim j As Integer
Dim filenumber As Integer
filenumber = Evaluate("CountA(A:A)")

Columns("A:A").Select
Selection.NumberFormat = "@"
j = 1
For i = 1 To filenumber
    If InStr(1, Range("A" & i), "xlsx") Then
    ActiveSheet.Range("B" & j).Value = ActiveSheet.Range("D1").Value & ActiveSheet.Range("A" & i).Value
    j = j + 1
    End If
Next i

    Columns("B:B").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:E").Select
    Application.CutCopyMode = False
    Selection.ClearContents

Dim xlfilenumber As Integer
Dim PL_list_length As Integer

xlfilenumber = Evaluate("CountA(A:A)")
ActiveSheet.Range("A1:A" & xlfilenumber).Select
Selection.Name = "list_of_files"

For i = 1 To xlfilenumber
    Range("B" & i).Select
        ActiveCell.FormulaR1C1 = _
        "=MID(RC[-1],FIND(""_PL"",RC[-1],FIND(""_PL\"",RC[-1],1)+4)+1,7)"
Next i

xlfilenumber = Evaluate("CountA(B:B)")
ActiveSheet.Range("A1:A" & xlfilenumber).Select
Selection.Name = "PL_compare_list"

Sheets(start_sheet).Activate
PL_list_length = Evaluate("CountA(F:F)") - 1



Dim h As Integer
Dim g As Integer
Dim filerownum As Integer
Dim matchrange As Range
Dim comparerange As Range
Dim filename As String

    For h = 6 To 9
            If IsEmpty(Range("J" & h)) Then
                matchrange = Workbooks("tracker test").Sheets(start_sheet).Range("F" & h).Value
                filerownum = Application.WorksheetFunction.Match(matchrange, Worksheets("Calculation Sheet").Range("PL_compare_list"), 0)
                    filename = Range("A" & filerownum).Value
                    Workbooks.Open filename
            End If

    Next h



Workbooks("tracker test").Sheets(start_sheet).Activate

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If


ActiveSheet.Cells(1, 1).Select
Application.CutCopyMode = False
Selection.ClearContents

ActiveSheet.Cells(1, 2).Select
Application.CutCopyMode = False
Selection.ClearContents

ActiveSheet.Cells(1, 3).Select
Application.CutCopyMode = False
Selection.ClearContents

End Sub

Try including the file extension in the workbook name. If "tracker test" is the name of the workbook containing your code then you can use use `ThisWorkbook` instead. — Tim Williams, Jul 24 '18 at 03:40
I changed it to be: matchrange = ThisWorkbook.Sheets(start_sheet).Range("F" & h).Value filerownum = Application.WorksheetFunction.Match(ThisWorkbook.Sheets(start_sheet).Range("F" & h).Value, Worksheets("Calculation Sheet").Range("PL_compare_list"), 0) but still no dice....getting the same error — Kyle Cranfill, Jul 24 '18 at 03:43
I may not be following fully, but I am trying to help. If PL are numbers and MatchRange is a range, does it make sense to assign the range to a number as opposed to its .value to a number? — Jeremy Kahan, Jul 24 '18 at 04:18
You'll get that error if there's no match for the searched value in the lookup range. — Tim Williams, Jul 24 '18 at 04:56
I see....that could be it....is there a way to wrap it in something that would allow it to input a message into the column H cell and then continue the loop? if so, how might that look? — Kyle Cranfill, Jul 24 '18 at 05:58

score 0 · Answer 1 · answered Jul 24 '18 at 06:14

0

Ideally there's a bunch of other changes you should consider, but to address the question of how to handle a failed Match:

Dim filerownum As Variant
Dim rngSrch As Range

Set rngSrch = Worksheets("Calculation Sheet").Range("PL_compare_list")

For h = 6 To 9
        If IsEmpty(Range("J" & h)) Then
            matchrange = Workbooks("tracker test").Sheets(start_sheet).Range("F" & h).Value
            'drop the WorksheetFunction
            filerownum = Application.Match(matchrange, rngSrch, 0)
            'test for error return value
            If Not IsError(filerownum) Then
                filename = Range("A" & filerownum).Value
                Workbooks.Open filename
            End If
        End If

Next h

answered Jul 24 '18 at 06:14

Tim Williams

154,628
8
97
125

1

Just to add to Tim answer, when calling an Excel function with `Application.WorksheetFunction.Function` syntax, errors will generate run-time errors that will be thrown (and can be trapped), while when using `Application.Function` syntax, errors code will be returned and can be tested with `IsError`. See this [page on Chip's site](http://www.cpearson.com/Excel/CallingWorksheetFunctionsInVBA.aspx). – Vincent G Jul 24 '18 at 06:57
Is it asking too much to say I'd love to hear your suggestions for the "Bunch of other things I should consider"? this is my first large macro i've ever tried to make – Kyle Cranfill Jul 24 '18 at 06:59
1

@KyleCranfill, Well, you could read [How to avoid using Select in Excel Macro](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Vincent G Jul 24 '18 at 07:03
Yes that's definitely the first place to go. – Tim Williams Jul 24 '18 at 14:54

Difficulty with the worksheetfunction.match property in VBA

1 Answers1