CODEWITHSUNDEEP
excelvbavlookup
Alessio_110
Alessio_110

Reputation: 143

Excel VBA Vlookup return error 2042 even if using IsError

The following code is not working. I get a 2042 error for my VLOOKUP function, however whatever I do I cannot solve it. I have been using if ISERROR and it still does not catch it properly compromising my whole macro. If I run a local window you can see that the value to search for being stored in array "arr" if not found in the "target" range return a 2042 even for subsequent entries.

Sub test()
ThisWorkbook.Activate

Worksheets.add

Worksheets("Test4").Range("A1:T110").copy Destination:=ActiveSheet.Range("A1")

With ActiveSheet

    Dim Search_Array As Variant
    Search_Array = Range("C2", Range("C1").End(xlDown)) 'use this array to loop through the value to search for


    Dim Target_MatchValue As Integer
    Dim Target_Range As Range
    Dim arr As Variant
    Dim counter As Integer
    Dim n As Integer



    counter = 0
    n = 0
    Target_MatchValue = 0

    For counter = LBound(Search_Array) To UBound(Search_Array)
        Target_MatchValue = 0

        Target_MatchValue = Application.Match(Search_Array(counter, 1), .Range("H2:H200"), 0) - 1 
        Set Target_Range = .Range(.Cells(2 + n, 8), .Cells(1000, 9))           

        arr = Application.VLookup(Search_Array(counter, 1), Target_Range, 2, False)

            If IsError(arr) Then
                .Range(Cells(1 + counter, 6), Cells(1 + counter, 6)).value = "N/A"
            Else
                .Range(Cells(1 + counter, 6), Cells(1 + counter, 6)).value = arr 'Return the value of the array in this cell
            End If



        Target_Range.Select

        If Target_MatchValue = 0 Then

            n = n + 1

            ElseIf Target_MatchValue > 0 Then
            n = n + Target_MatchValue
        End If



Next counter

End With

End Sub

SOLUTION

Sub test()

Dim Search_Array As Variant
Dim Target_MatchValue As Variant
Dim Target_Range As Range
Dim arr As Variant
Dim counter As Integer
Dim n As Integer



Worksheets("Test4").Range("A1:T110").copy Destination:=ActiveSheet.Range("A1")
With ActiveSheet

'data must be ordered in order to apply the non-repetitive condition
Search_Array = Sheet1.Range("A2", Sheet1.Range("A1").End(xlDown)) 'use this array to loop through the value to search for


n = 0

With ActiveSheet
    For counter = LBound(Search_Array) To UBound(Search_Array)

        Target_MatchValue = 0
        Target_MatchValue = Application.Match(Search_Array(counter, 1), .Range(Cells(2 + n, 4), Cells(1000, 4)), 0) 'The problem was here. "A1:T110" did not allowed to the shifting range to change. Now this code will return the value used for the shifting range
        Set Target_Range = .Range(Cells(2 + n, 4), Cells(1000, 5))  'this is supposed to work as a shifting range allowing to match entries without making repetitions. I used the MATCH function in order to set the start of the range. i.e. if there is a match in the target table the range will shift from the location of the match downwards. If the match is at on the same level then it does not shift the range in order to match the same-level entry afterwards it is supposed to shift by one unit in order to prevent repetitions.
        'target_range.select Activate this code in order to see the macro in action
        arr = Application.VLookup(Search_Array(counter, 1), Target_Range, 2, False) 'store the vlookup value in an array in order to increase the efficiency the code and to speed up the whole proces

            If IsError(arr) Then
                .Cells(2 + n, 2).value = "" 'if the macro does not find anything, no value will be recorded anywhere

                Else
                .Cells(1 + n + Target_MatchValue, 2).value = Search_Array(counter, 2)  'Return the value of the search_array in this cell so to match column A values with column D values if they are found

            End If

            If IsError(arr) Then
                    n = n
                ElseIf Target_MatchValue = 0 Then 'if the macro does not find anything, the shifting range does not shift so that subsequent values can be searched in the same range without missing precious matches
                    n = n + 1

                ElseIf Target_MatchValue > 0 Then 'if there is a matching value between Column A and Column B, the shifting range shifts by the n + the distance between the the current vlookupvalue and the found value. Note that Data must be stored in a filtered order otherwise vlookup will not work correctly
                    n = n + Target_MatchValue

            End If
    Next counter

End With

End Sub

Upvotes: 0

Views: 562

Answers (1)

Damian
Damian

Reputation: 5174

Declare your Target_MatchValue As Variant so no errors will be raised, instead you will have to handle what do you want to do when IsError(Target_MatchValue) (when no matches are found)

Upvotes: 1

Related Questions