Excel: Error with index match function


Hey all! My code looks up a user input value and returns a value from a different column a row below. This works great when I use VLookup until I realized I couldn't use the OFFSET function in VBA. So after looking through the forums, I switched to using INDEX-MATCH combination, which throws me a Error 1004, unable to find Index property. VLookup:

CableSizeTable = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26,78))
CableColumn = 79-Column
CableSize = WorksheetFunction.VLookup(Val(LoadCurrentText), CableSizeTable, CableColumn)
INDEX-MATCH:

CableColumn = Columns(1)
ConductorRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column))
j = 1
CableSize = WorksheetFunction.Index(CableColumn, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1), -j).Value
I've tried changing

CableColumn = Columns(1)
CableColumn = Range(Columns(1))
CableColumn = Range("A:A")
but there's no difference. Thanks in advance!


This question generated 27 answers. To proceed to the answers, click here.

This thread is current as of March 06, 2014.


For more resources for Microsoft Excel