Error with index match function

mcbrocks

New Member
Joined
Mar 6, 2014
Messages
31
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:
Code:
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:
Code:
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
Code:
CableColumn = Columns(1)
CableColumn = Range(Columns(1))
CableColumn = Range("A:A")
but there's no difference.
Thanks in advance!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi

Try this :-
Code:
Dim CableColumn As Range
Set CableColumn = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


hth
 
Upvote 0
Thanks ukmikeb!
I've copied into my code .... but I can't try it as some similar lines before it is causing the same problem
Code:
    VoltageDropColumnDC = Columns(10)
    VoltageDropColumnR = Columns(Column + 1)
    VoltageDropColumnX = Columns(Column + 2)
    Dim VoltageDropColumnDC As Range
    Dim VoltageDropColumnR As Range
    Dim VoltageDropColumnX As Range
    Set VoltageDropColumnDC = Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
I used the format you provided but am stumped at VoltageDropColumnR & X. Column is an integer .
 
Upvote 0
Hi

My response was based on this statement :-
Code:
CableSize = WorksheetFunction.Index(CableColumn, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1), -j).Value

in which the subject of Index has to be a Range.

In the Vlookup section you seem to have assigned a Range to CableSizeTable, shouldn't that be the subject of your Index?

Maybe a broader description of your CableSizeTable and what results you should get from the Vlookup and Match functions would help.
 
Upvote 0
Hi ukmikeb,
I'm not sure how to upload a picture of what the data sheet looks like, so I'll describe it.

Searched Column : Column B to H, only one column depending on user options.
Return Column: Column A (CableColumn) and I (VoltageDropColumn)
My rows are split into 2 parts, from Row 10-26 and Row 28-49. According to the user input, it searches the data in the specific column, within the row part.

In the same data sheet, From Column J onwards, the format is different. There are 4 columns in a set.
One set is from: Column J to M.
Column J = Column B to H, the column to be searched.
Column K = Column L = Column I.
3 values to be returned, Column A, K, L.

When using Vlookup, I couldn't return the cablecolumn row, so I duplicated this column into Column BZ. Which results in the cablesizetable ending at column 78. But with index match I have no use for column 78.

I hope I've explained it clearly.
 
Upvote 0
Hi

Would this work :-
Code:
CableSize = WorksheetFunction.Index(CableSizeTable, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1), 1).Value

or even this :-
Code:
CableSize = WorksheetFunction.Index(ConductorRow, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1), 1).Value

with the version you quoted in the OP you referenced the result column as -j which would have evaluated as zero giving you your error.

In your OP you said:-
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.
Yes you can use offset in VBA :-
Code:
Range("A2"),Offset(-1,1).Value
which would give you the value of B1.

Look at my signature for options on posting sections of spreadsheets or alternatively use something like Dropbox to share the worksheet.
NB Do neutralize the sheet where sensitive information is concerned.

hth
 
Upvote 0
Hi ukmikeb,
Actually I meant the offset using a value not a range. I tried all the codes and they are still giving the same error. Cna't find index property. I can't access any shared folder websites on this office laptop, and the links you provided at the bottom only work on Office 2007 onwards, so I too can't use them as I'm running on Office 2003. It's found in this post :Error when running VLookup - Page 2
 
Upvote 0
The code is running now, but the value it returns is empty. I'm using this:
Code:
    Dim VoltageDropColumnDC As Range
    Set VoltageDropColumnDC = Columns(10)


VoltageDropDC = WorksheetFunction.Index(VoltageDropColumnDC, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) - 1)
DesignCurrent = MotorRatingText / ((EfficiencyText / 100) * DCText)
CalculatedVoltageDrop = (VoltageDropDC * DesignCurrent * CableLengthText) / 1000

Inputs:
LoadCurrentText = 200
MotorRatingText = 1000
EfficiencyText = 100
DCText = 10

Outputs:
VoltageDropDC = empty
CalculatedVoltageDrop = 0
DesignCurrent = 100
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top