Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Error with index match function

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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:
    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!

  2. #2
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    Hi

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

    hth
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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 .

  4. #4
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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.

  6. #6
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    I just tried your code for the cablesize and it still gives the same error.

  7. #7
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  8. #8
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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

  9. #9
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    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 by mcbrocks; Mar 10th, 2014 at 05:58 AM.

  10. #10
    New Member
    Join Date
    Mar 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error with index match function

    Here's an update, the VoltageDropDC now returns a string from the worksheet but is placed before my loop begins. Returns Row 8 value when by loop begins at Row 10.
    Here's the updated code : https://www.dropbox.com/s/w280jdk5hx...2011Mar-2.xlsm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •