Index Match with VBA Different Sheet

[bhalbach]

I'm really new to VBA and slowly learning but I am struggling with Index and Match.

I have the following code that works great if the Data starts in A1 on both the "Data" sheet and the "VBA Output" sheet.

1. change the LookRange to start at "D9", instead of "A:A"
2. change the SourceRange to "D8:P8"
3. change the Header_Range to "D8:P8
4. the data to lookup starts at D12 (Header) and the last row is D27

I hope I have explained this correctly...I have been working on this forever and just don't understand it. I hope the copied table layout shows correctly.

Please help....




Sub IndexMatch_Function()

Dim x As Integer, y As Integer
Dim SourceRange As Range
Dim LookRange As Range
Dim Header_Range As Range
Dim ws As Worksheet

Set wsD = Sheets("Data")
Set wsVO = Sheets("VBA Output")
Set SourceRange = wsD.Range("A:E")
Set LookRange = wsD.Range("A:A")
Set Header_Range = wsD.Range("1:1")

wsVO.Select

MyLastRow = wsVO.Cells(Rows.Count, 1).End(xlUp).Row
MyLastColumn = wsVO.Cells(1, Columns.Count).End(xlToLeft).Column

For x = 2 To MyLastRow
For y = 2 To MyLastColumn

On Error Resume Next

wsVO.Cells(x, y) = WorksheetFunction.Index(SourceRange, _
WorksheetFunction.Match(wsVO.Cells(x, 1), LookRange, 0), _
WorksheetFunction.Match(wsVO.Cells(1, y), Header_Range, 0))

Next y
Next x

On Error GoTo 0

End Sub



Sheet "Data" (this is a Table "tblLabour") - Header starts at D8 (Dynamic and can grow in rows)

Description

Base Rate

Adjusted Base Rate

Overtime

Subtotal Base Rate

Vacation Rate

Stat Holiday Rate

Employer EI Rate

Employer CPP Rate

Employer WCB Rate

Health Benefits

Pension Rate

Burden Rate

Concrete Finisher

29.00​

29.00​

7.25​

36.25​

2.09​

1.16​

0.80​

1.90​

0.94​

0.00​

0.00​

43.15​

Excavator Operator

30.00​

30.00​

7.50​

37.50​

2.16​

1.20​

0.83​

1.97​

0.98​

0.00​

0.00​

44.64​

Extruder Operator

30.00​

30.00​

7.50​

37.50​

2.16​

1.20​

0.83​

1.97​

0.98​

0.00​

0.00​

44.64​

Foreman

35.00​

35.00​

8.75​

43.75​

2.52​

1.40​

0.97​

2.30​

1.14​

0.00​

0.00​

52.08​

Form Setter

26.00​

26.00​

6.50​

32.50​

1.88​

1.04​

0.72​

1.71​

0.85​

0.00​

0.00​

38.68​

General Labour

23.00​

23.00​

5.75​

28.75​

1.66​

0.92​

0.64​

1.51​

0.75​

0.00​

0.00​

34.22​

General Operator

27.00​

27.00​

6.75​

33.75​

1.95​

1.08​

0.75​

1.77​

0.88​

0.00​

0.00​

40.17​

Grader Operator

31.00​

31.00​

7.75​

38.75​

2.24​

1.24​

0.86​

2.03​

1.01​

0.00​

0.00​

46.12​

Line Setter

26.00​

26.00​

6.50​

32.50​

1.88​

1.04​

0.72​

1.71​

0.85​

0.00​

0.00​

38.68​

Milling Operator

30.00​

30.00​

7.50​

37.50​

2.16​

1.20​

0.83​

1.97​

0.98​

0.00​

0.00​

44.64​

Paver Operator

29.00​

29.00​

7.25​

36.25​

2.09​

1.16​

0.80​

1.90​

0.94​

0.00​

0.00​

43.15​

Rakeman

29.00​

29.00​

7.25​

36.25​

2.09​

1.16​

0.80​

1.90​

0.94​

0.00​

0.00​

43.15​

Screed Man

29.00​

29.00​

7.25​

36.25​

2.09​

1.16​

0.80​

1.90​

0.94​

0.00​

0.00​

43.15​

Skidsteer Operator

27.00​

27.00​

6.75​

33.75​

1.95​

1.08​

0.75​

1.77​

0.88​

0.00​

0.00​

40.17​

Skilled Labour

25.00​

25.00​

6.25​

31.25​

1.80​

1.00​

0.69​

1.64​

0.81​

0.00​

0.00​

37.20​

Supervisor

40.00​

40.00​

10.00​

50.00​

2.89​

1.60​

1.11​

2.63​

1.30​

0.00​

0.00​

59.52​

Truck Driver

24.00​

24.00​

6.00​

30.00​

1.73​

0.96​

0.66​

1.58​

0.78​

0.00​

0.00​

35.71​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

0.00​

Sheet "VBA Output" (Not a Table) - Header D12 (Only 16 rows including header)

Description	Each	Utilization	Hrs	Burden Rate	Extension
Concrete Finisher	1.00	100%​	12​	$ 43.15	$ 517.78
Excavator Operator	1.00	100%​	12​	$ 44.64	$ 535.64
Extruder Operator	1.00	100%​	12​	$ 44.64	$ 535.64
Milling Operator	1.00	100%​	12​	$ 44.64	$ 535.64
Paver Operator	1.00	100%​	12​	$ 43.15	$ 517.78
Rakeman	1.00	100%​	12​	$ 43.15	$ 517.78
Screed Man	1.00	50%​	12​	$ 43.15	$ 258.89
Skidsteer Operator	1.00	50%​	12​	$ 40.17	$ 241.04