mardi 28 mars 2017

Using a VLOOKUP in VBA - Taking Care of N/A's by referencing a different VLOOKUP or INDEX/MATCH

I am having trouble figuring out this IF-THEN statement in VBA. The code I'm using currently uses a VLOOKUP to pull values into Column E, but some results come in as #N/A because they are not contained the tab which the VLOOKUP is referencing. If a result comes in as #N/A, I need VBA to do another lookup (preferably Index/Match) on a different tab within my workbook. This is what I have so far, step 6 works perfect but returns some N/A's. The error is coming from step 7 as I am trying to address the N/A's with the IF-THEN statement:

'Step 6: Copy the recordset to Excel

 With ThisWorkbook.Worksheets("DQ_Collectors_3162")
    .Activate
    .Range("A2:R" & Rows.Count).Clear
    .Range("A2").CopyFromRecordset rs

    'Dealer column
    Dim dealerCol As String
    Dim FinalRow As Integer
    Dim contractCol As String


    contractCol = ConvertToLetter(FindMatchingValueColumn("Contract ID", 1))
    FinalRow = .Cells(Application.Rows.Count, 1).End(xlUp).Row + 1

    lookup = "=VLOOKUP(" & contractCol & ":" & contractCol & ",'Dealer Name 
    Index'!A:B,2,FALSE)"
    dealerCol = ConvertToLetter(FindMatchingValueColumn("Dealer", 1))
    .Range(dealerCol & "2").Value = vlookup
    .Range(dealerCol & "2:" & dealerCol & FinalRow).FillDown
    .Range(dealerCol & ":" & dealerCol).Copy

End With

'Step 7: Take Care of N/A's

    Dim R As Range
    indexmatch = "=INDEX('IBIC Name Index'B:B,match(B:B,'IBIC Name 
    Index'A:A,0))"

    Set R = Range("E:E")
    If R.Value = "#N/A" Then
    R.Value = indexmatch

End If

The error I'm getting is - Run-Time error '13': Type Mismatch

I'm new to VBA and I think this is an easy fix but I can't seem to get it. Thanks for the help!

Aucun commentaire:

Enregistrer un commentaire