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
VBA Code:
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) |
Last edited by a moderator: