CODEWITHSUNDEEP
excelvbamatch
Beans
Beans

Reputation: 139

Index/Match Loop in VBA

I am trying to convert an Index/Match Excel formula to VBA, but somehow it’s not working.

I have two sheets. One is called “ExcludeInclude”, the other one is called “PCAM Commitments”. “ExcludeInclude” sheet looks like this: ExcludeInclude

“PCAM Commitments” sheet looks like this: PCAM Commitments

Basically, all I am trying to do is to match the Exclude/Include values. For example, “SO045828757” shows as “Exclude” on the “ExcludeInclude” sheet. I need to display the Exclude/Include values in Column G on the “PCAM Commitments” sheet (using a combination of loop/index/match).

This excel formula will do the job:

=INDEX(ExcludeInclude!$B$2:$C$136, MATCH('PCAM Commitments'!A2,ExcludeInclude!$B$2:$B$136,0),2)

But I need to convert this formula to VBA.

This is what I have"

Sub ExIn()

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

lookRange = Sheets("ExcludeInclude").Range("B2:C136")
StartRange = Sheets("ExcludeInclude").Range("B2:B136")

For i = 2 To 136
    Worksheets("PCAM Commitments").Cells(i, 7) = WorksheetFunction.Index(lookRange, WorksheetFunction.Match(Worksheets("PCAM Commitments").Cells(i, 1).Value, StartRange, 0), 2)

Next i

End Sub

But it's not working, whenever I try to run it, an error message box will pop up saying "Object Variable or With Block Variable not set".

Any help would be appreciated! Thank you!

Upvotes: 0

Views: 383

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Set lookRange = Sheets("ExcludeInclude").Range("B2:C136")
Set StartRange = Sheets("ExcludeInclude").Range("B2:B136")

Upvotes: 0

Related Questions