• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Runtime Error 1004 Index Match with Variable as Criteria

Reggieneo

Member
Hi All,
May I ask assistance on this?

this code gives me "Run-time error '1004:"
Code:
Application.WorksheetFunction.Index(Sheets("Sheet1").Range(NameRange1), Application.WorksheetFunction.Match("Hello", Sheets("Sheet1").Range(NameRange2), 0) - 1)

this works well:
[CODE]
Application.WorksheetFunction.Index(Sheets("Sheet1").Range("D1:D100"), Application.WorksheetFunction.Match("18-1113095", Sheets("Sheet1t").Range("A1:A100"), 0) - 1)

Reason I need to put a name range or variable is I need to have constantly changing ranges.

Much thanks,
reggieneo
 
Reggieno

Firstly, Welcome to the Chandoo.org Forums

Can you try:
Code:
Application.WorksheetFunction.Index(Sheets("Sheet1").Range([NameRange1]), Application.WorksheetFunction.Match("Hello", Sheets("Sheet1").Range([NameRange2]), 0) - 1)
 
Hi ,

Both of the following work :
Code:
Public Sub temp()
     x = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("NameRange1"), Application.WorksheetFunction.Match("18-1113095", Sheets("Sheet1t").Range("NameRange2"), 0) - 1)
     MsgBox x
     x = Application.WorksheetFunction.Index([NameRange1], Application.WorksheetFunction.Match("18-1113095", [NameRange2], 0) - 1)
     MsgBox x
End Sub
In your code , the working version uses Sheet1t ; I assume this is not a typo.

Narayan
 
Back
Top