Vba index/match function not working

drluke

Active Member
Joined
Apr 17, 2014
Messages
283
I am trying to do a simple INDEX/MATCH using vba, but without success.

I have 2 worksheets (I would attach a sample but I don't have permission)
Sheet"Journal" - holding criteria for my lookup. D7 to end = 1st lookup. E7 to end = 2nd lookup. I7 to end is where the result goes
The values in col D are all the same for values in col E (which varies). Ie. D7=3100 E7=25, D8=3100 E8=81, D9=3100 E9=101 and so on
The range of values in col E covers 18 rows before the value in col D changes, so D19=3200 E19=25 etc
Sheet "Volume Allocation" - holds lookup ranges. Row 8 starting at col E to end is 1st range. Col B9 to end is 2nd range
Indexed range starts at E9 to the lastrow in last column
The latest code I've tried is below, but doesn't work. I have no idea why. Any advice would be appreciated.
Code:
With ThisWorkbook.Worksheets("Journal")
        El1 = Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
        El2 = Range("E8:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
        LCol = ThisWorkbook.Worksheets("Volume Allocation").Cells(8, .Columns.Count).End(xlToLeft).Offset(, -1).Column
        LRow1 = ThisWorkbook.Worksheets("Volume Allocation").Range("B9:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        LastRow = Range("I8:I" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        LRow = ThisWorkbook.Worksheets("Volume Allocation").Range("E9:BJ").Find(What:="*", _
                      After:=.Range("E9"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
            For q = 8 To LastRow
                Application.WorksheetFunction.Index(Sheets("Volume Allocation").Range("E9" & LRow),Application.WorksheetFunction.Match _
                (Sheets("Journal").Range("El1"),Sheets("Volume Allocation").Range("LCol"), 0),Application.WorksheetFunction.Match _
                (Sheets("Journal").Range("El2"),Sheets("Volume Allocation").Range("LRow1"), 0))
            Next q
        End With
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top