Index(Match()) Within For Loop VBA Code Help

[tjgiles2]

Hello,

Im trying to populate cells using a an index match function within VBA, I know how to do this with in a cell but cant seem to get it to work in vba. The code segment below is what i have so far, the underlined part is the line i am haivng trouble with. Thanks for help it would be much appriciated.

Sub Merge()
Dim ws1, ws2 As Worksheet
Dim wb As Workbook
Dim a, b, PSum, StdHrs, LastRow As Integer
Set wb = ThisWorkbook
Set ws1 = wb.Sheets("DOWNLOAD2")
Set ws2 = wb.Sheets("JOHN")
a = 2
b = 2
PSum = 0
StdHrs = 0
Application.ScreenUpdating = False
LastRow = ws2.UsedRange.Rows.Count
For a = 2 To LastRow
If ws2.Cells(a, 4).Value <> ws2.Cells(a + 1, 4).Value Then

ws2.Cells(a, 22) = Application.WorksheetFunction.Index(Sheets("DOWNLOAD").Range("A:Q"), Application.WorksheetFunction.Match((ws2.Cells(a,3).Value) & (ws2.Cells(a,4).Value), (Sheets("DOWNLOAD").Range("A:A")) & (Sheets("DOWNLOAD").Range("C:C")), 0), 16)

End If
Next a
Application.ScreenUpdating = True

End Sub

 

[Rijnsent]

Hi tjgiles2,

try recording a macro and type the function in a cell. I got something like this:
ActiveCell.FormulaR1C1 = "=INDEX(R[-5]C[-5]:R[-2]C[-2],MATCH(R[-7]C[-3],R[-8]C[2]:R[9]C[2],0),3)"

You could easily put that in place instead of the formula you're trying to build right now. If need be: put formula in place, copy down for all rows, calculate, copy whole range, paste values over whole range . Whenever I use the Application.Worksheetfunction, I tend to start with "Set WSF = Application.WorksheetFunction" in my code, that simplifies the lines where you have to use a couple of them, like in your underlined example.

Cheers,

Koen