IFERROR with INDEX/MATCH in VBA

Question

I have written the following code to bring values from an external sheet into my workbook using a index match loop. If the error occurs the index/match formula it is supposed to retrieve another value from a worksheet within my current workbook.

The loop works until it gets to the first value that it is supposed to bring in from the worksheet within my current workbook.

And ideas on how I can fix the IF ISERROR loop to bring in the values?

Sub UpdateFile()

    Dim wbMVRVFile As Workbook
    Dim wbNewMV As Workbook
    Dim wsRevFile As Worksheet
    Dim wsMvFile As Worksheet
    Dim wsMvOld As Worksheet
    Dim wsRevOld As Worksheet
    Dim wsNewMV As Worksheet
    Dim wsTempFile As Worksheet
    Dim FrRngCount As Range
    Dim i As Integer
    Dim b As Integer
    Dim y As Integer


    Set wbMVRVFile = Workbooks("Databook_2016.xlsm")

    Set wsMvOld = wbMVRVFile.Worksheets(2)
    Set wsRevOld = wbMVRVFile.Worksheets(1)
    Set wsTempFile = wbMVRVFile.Worksheets("TempFile")

    wbMVRVFile.Worksheets.Add().Name = "MV " & Format(DateSerial(Year(Date), Month(Date), 0), "dd-mm-yy")

    Set wsMvFile = wbMVRVFile.ActiveSheet

    Set FrRngCount = wsMvOld.Range("A:A")
    i = Application.WorksheetFunction.CountA(FrRngCount)

    wsTempFile.Range("A1:A" & i).Value = wsMvOld.Range("A1:A" & i).Value

    Set wbNewMV = Workbooks.Open("F:\Reports\Data\NReport" &         Format(DateSerial(Year(Date), Month(Date), 0), "yyyymmdd") & ".xls")
    Set wsNewMV = wbNewMV.Worksheets(1)

    Set FrRngCount = wsNewMV.Range("B:B")
    y = Application.WorksheetFunction.CountA(FrRngCount)

    b = i + y - 2

    wsTempFile.Range("A" & i & ":A" & b).Value = wsNewMV.Range("B2:B" & y).Value
    wsTempFile.Range("A1:A" & b).AdvancedFilter Action:=xlFilterCopy,     CopyToRange:=wsMvFile.Range("A1"), Unique:=True


    Set FrRngCount = wsMvFile.Range("A:A")
    y = Application.WorksheetFunction.CountA(FrRngCount)

    'i = 2

    For i = 2 To y

    If Not IsError(wsMvFile.Range("B" & i) = Application.WorksheetFunction.Index(wsNewMV.Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row), Application.WorksheetFunction.Match(wsMvFile.Range("A" & i), wsNewMV.Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row), 0), 1)) Then

    wsMvFile.Range("B" & i) = Application.WorksheetFunction.Index(wsMvOld.Range("B1:B" & Cells(Rows.Count, "C").End(xlUp).Row), Application.WorksheetFunction.Match(wsMvFile.Range("A" & i), wsMvOld.Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row), 0), 1)

    End If

    Next i

End Sub

`wsMvOld.Range("B1:B" & Cells(Rows.Count, "C").End(xlUp).Row` is just plain wrong. Qualify the `.Cells` parent worksheet. See [this](http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394). — , Apr 15 '16 at 17:58

score 1 · Accepted Answer · 2016-04-15T19:29:12.380

1

You need to qualify the Range.Parent property of the Range.Cells property that you are using to define the Range object if you continue with this method of referencing a range.

However, you can abandon that method in favor of the more succinct Range.Columns property.

A WorksheetFunction object is slightly different than a strictly Excel Application object. The IsError function responds better to the latter as the error can be carried into a variant type variable and examined rather than throwing an runtime error.

Dim vrw As Variant

For i = 2 To y
    vrw = Application.Match(wsMvFile.Range("A" & i), wsNewMV.Columns(2), 0)
    If IsError(vrw) Then
        vrw = Application.Match(wsMvFile.Range("A" & i), wsMvOld.Columns(1), 0)
        If Not IsError(vrw) Then _
            wsMvFile.Range("B" & i) = Application.Index(wsMvOld.Columns(2), vrw)
    Else
        wsMvFile.Range("B" & i) = Application.Index(wsNewMV.Columns(3), vrw, 1)
    End If
Next i

This might benefit from referencing one of the worksheets in a With ... End With statement outside the loop, especially so as you are repeating the references within a loop but I've left everything redundantly referenced for now.

Of course, you could always just write the formulas into all of the cells at once and then revert them to their returned values.

edited Apr 15 '16 at 19:29

answered Apr 15 '16 at 18:14

Thanks for your help Jeeped. When I run your code I get a compile error "Argument not optional". It looks like it has something to do with the range definition for this part wsMvOld.Range.Columns(1). Any ideas? – VBA Pete Apr 15 '16 at 18:32
It was `wsMvOld.Columns(1)` not `wsMvOld.Range.Columns(1)`. – Apr 15 '16 at 18:41
Yes, that fixed it. Thanks for that. But when I run the code it still jumps out of the loop as soon as it is supposed to retrieve the value from the backup source (the worksheet within my current workbook). Why would this occur? – VBA Pete Apr 15 '16 at 18:51
*'jumps out of the loop'* tells me that it tries to process `wsMvFile.Range("A" & i)` and then immediately exits the loop and tries nothing else. I don't see how that could happen. – Apr 15 '16 at 18:56
btw, your original two worksheetfunction.index lines looked at two different things. I chose the latter of the two. Perhaps you meant the former. – Apr 15 '16 at 18:58
Yes, my index lines look at two different thing, because if the value is not in the wsNewMV sheet it is supposed to look it up in the wsMvOld sheet, where it is guaranteed to have a value for it. – VBA Pete Apr 15 '16 at 19:21
Just for my clarification, the loop will run through every i until it reaches y, right? – VBA Pete Apr 15 '16 at 19:21
OK, I think I understand what the two were intended to do. See my mods above. – Apr 15 '16 at 19:29
Yes, that's it. Thanks so much @Jeeped! – VBA Pete Apr 15 '16 at 19:39

IFERROR with INDEX/MATCH in VBA

1 Answers1