VBA index match code fails to work on certain cell

[mychi11]

Hi all, my vba code is as follow, it successfully index match the price but not for cell E53 onward. Could any expert 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 wsA = Sheets("Blood_Cost")
Set wsVO = Sheets("Blood_Price")
Set SourceRange = wsA.Range("A:B")
Set LookRange = wsA.Range("A:A")
Set Header_Range = wsA.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, 2), LookRange, 0), _
WorksheetFunction.Match(wsVO.Cells(1, y), Header_Range, 0))

Next y
    Next x

On Error GoTo 0

End Sub

Purchase Order and Budget Estimation.xlsm
	A	B	C	D	E	F	G
1		Test	Female	Male	Cost	Margin	Final Price
40		ANF	1		114	150%	171
41		FSH	3		152	150%	684
42		LH	3		121.6	150%	547.2
43		E2	3		152	150%	684
44		Prolaction	1		121.6	150%	182.4
45		Testosterone	1		190	150%	285
46		DHEA	1		171	150%	256.5
47		Androstenedione	1		744.8	150%	1117.2
48		P4	1		18.9	150%	28.35
49		AMH	1		706.8	150%	1060.2
50		Thalassemia Screening	1		495.4	150%	743.1
51							7339.05
52	IVF/ICSI Treatment Sexual Disease	HBsAg	1	1	68.4	150%	205.2
53		Anti-HCV	1	1		150%	0
54		VDRL	1	1		150%	0
55		HIV Ab	1	1		150%	0
56							205.2
Blood_Price

Cell Formulas
Range	Formula
G52:G55,G40:G50	G40	=E40*(C40+D40)*F40
G51	G51	=SUM(G36:G50)
G56	G56	=SUM(G52:G55)

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added them for you this time.

it successfully index match the price but not for cell E53 onward. Could any expert please help

In column A you have merged cells so your code it evaluating MyLastRow as row 52 and that is why it is stopping there. Since column B appear to not have merged cells, try swapping to that to get MyLastRow

MyLastRow = wsVO.Cells(Rows.Count, 1).End(xlUp).Row
MyLastRow = wsVO.Cells(Rows.Count, 2).End(xlUp).Row

 

[mychi11]

thank you so much, this is working perfectly

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.