How if use IFNA in VBA (combined with Index/Match)

Question

I have two sheets. One is called “Exclusions”, the other one is called “PCAM Commitments.” Exclusions sheet ONLY contains the projects with a status of “Exclude”. PCAM Commitments sheet have all the projects (so both “Include” and “Exclude” projects). Exclusions Sheet; PCAM Commitments Sheet

I need to match these Exclude/Include values in Column G of the PCAM Commitments sheet. Since projects with a status of “Include” are not on the “Exclusions” sheet, I need to use IFNA function.

This formula will do the job: =IFNA(INDEX(Exclusions!B2:C100, MATCH('PCAM Commitments'!A2,Exclusions!B2:B100,0),2), "Include")

But I am having troubles to convert this formula to vba code.

Sub ExIn()

Dim i As Integer
Dim lookRange As Range
Dim StartRange As Range
Dim LastRow As Integer

Dim ID As Long
Dim Value As Long

ID = Application.Match("PO/SO", Rows(1), 0)
Value = Application.Match("Ex/In", Rows(1), 0)

LastRow = Sheets("PCAM Commitments").Cells(Rows.Count, "A").End(xlUp).Row
Set lookRange = Sheets("Exclusions").Range("B2:C136")
Set StartRange = Sheets("Exclusions").Range("B2:B136")


For i = 2 To LastRow
    Worksheets("PCAM Commitments").Cells(i, Value) = WorksheetFunction.IfNa(WorksheetFunction.Index(lookRange, WorksheetFunction.Match(Worksheets("PCAM Commitments").Cells(i, ID).Value, StartRange, 0), 2), "Include")

Next i

End Sub

This is what I have now. Whenever I run it, an error message will pop up saying “Unable to get the Match property of the WorksheetFunction class…. Any help would be appreciated!

WorksheetFunction.Match will throw that error if no match. One alternative is to use Application.Match and then test if the result is an error with IsError. — BigBen
You mean like this: " Worksheets("PCAM Commitments").Cells(i, Value) = WorksheetFunction.IsError(WorksheetFunction.Index(lookRange, Application.Match(Worksheets("PCAM Commitments").Cells(i, ID).Value, StartRange, 0), 2), "Include") "?...It's not working either — EmmaG
Not WorksheetFunction.IsError, but the IsError function. You'll need to split this out into multiple lines. — BigBen
Another option is to use Evaluate with your original formula. — BigBen
In my opinion, the best option will be to get rid of long WorksheetFunction and use other vba tools. Do I understand correctly that you need to go through sheet PCAM Commitments, take a pair of values from columns A and B, find same pair on Exclusions sheet in columns C and B, and fill the column G of PCAM Commitments sheet appropriately? — Vitaliy Prushak

EvR EvR · Accepted Answer · 2019-12-03T20:14:43

Try it with Vlookup and Application instead of Worksheetfunction,like this:

With Application
For i = 2 To LastRow
    Worksheets("PCAM Commitments").Cells(i, Value) = .IfNa(.VLookup(Worksheets("PCAM Commitments").Cells(i, ID).Value, lookRange, 2, False), "Include")
Next i
End With

How if use IFNA in VBA (combined with Index/Match)

1 Answers