VBA - INDEX,MATCH with named ranges (Confused as!!)

[Scorpion Steve]

Hi all!
Happy New years to start of with.
I'm trying to use two different named ranges nested within a string using the INDEX/MATCH function.

I've tried everything and still getting errors:
Run-time error '13':
Type Mismatch
for:

Application.WorksheetFunction.Index(SearchInput, rng1, Application.Match(SearchInput, rng2, 0, 1)) = ContactNo.Value

&
Run-time error '1004':
Invalid number of arguments.

Application.WorksheetFunction.Index(SearchInput, Range("rng1"), Application.Match(SearchInput, Range("rng2"), 0, 1)) = ContactNo.Value

Code snippet:

Dim rng1 As Range
        Set rng1 = ActiveWorkbook.Names("PCIDRange").RefersToRange
           Dim ws As Worksheet
                Set ws = ActiveWorkbook.Names("PCIDRange").RefersToRange.Worksheet
    Dim rng2 As Range
        Set rng2 = ActiveWorkbook.Names("ContactNoRange").RefersToRange
           Dim ws2 As Worksheet
                Set ws2 = ActiveWorkbook.Names("ContactNoRange").RefersToRange.Worksheet


[COLOR=#0000ff]Application.WorksheetFunction.Index(SearchInput, rng1, Application.Match(SearchInput, rng2, 0, 1)) = ContactNo.Value[/COLOR]
End If

Anyone got a clue?
Ta

 

[Momentman]

That syntax should be the other way round I think

ContactNo.Value = Application.WorksheetFunction.Index(SearchInput, rng1, Application.Match(SearchInput, rng2, 0, 1)) 

Give that a try. But then again, the second argument "rng1" should be a number so i dont know how you want to pull that off

 

[Scorpion Steve]

Hi Momentman.
I see what you're saying.
I'm trying to update the cell from the value within textbox ("SearchInput")

I think the way round that you suggest will update the textbox value instead of the cell value

But its a start on mucking around with where I place the ".value"

ta

 

[jsotola]

you are using vba to run a cell formula

the formula has to work in a cell in a worksheet

the formula does not expect a range object, it expects a string in the form of a range name ("PCIDRange") or range definition ("A5:G7" or "A1")

therefore, try putting the range names into the formula

Application.WorksheetFunction.Index(SearchInput, "PCIDRange", Application.Match(SearchInput, "ContactNoRange", 0, 1)) = ContactNo.Value

it also looks like you have the two sides of the equation reversed

maube it should be ContactNo.Value = ............

make the formula work in a cell first

then copy it into the vba code and make it a comment so that vba does not flag it as an error

then work from that as a guide

 

[Scorpion Steve]

Now I have Run-time '1004'
Method of 'Range' of object'_Global' failed
when:
Code:

Dim rng1 As Range
        Set rng1 = ActiveWorkbook.Names("PCIDRange").RefersToRange
           Dim ws As Worksheet
                Set ws = ActiveWorkbook.Names("PCIDRange").RefersToRange.Worksheet
    Dim rng2 As Range
        Set rng2 = ActiveWorkbook.Names("ContactNoRange").RefersToRange
           Dim ws2 As Worksheet
                Set ws2 = ActiveWorkbook.Names("ContactNoRange").RefersToRange.Worksheet


[COLOR=#0000ff]With Application.WorksheetFunction.Index(SearchInput, Range(rng1), Application.Match(SearchInput, Range(rng2), 0, 1))[/COLOR]
[COLOR=#0000ff].Value = ContactNo[/COLOR]
End With
End If

 

[Scorpion Steve]

Hi Justola.
Yeah, I started with that :/
Type mismatch

It works fine if I hard code the range but I'm needing it to be dynamic.
Well, having said that, I don't - I can be done hard coded - obviously.
I think I'm being too picky and thought there may be a simple solution.

I have over 200 dynamic ranges

Steve

 

[Scorpion Steve]

Hard Coded still doesn't work :?
Application.Index(Sheets("DATA SHEET").Range("G2:G250"), Application.Match(SearchInput, Sheets("DATA SHEET").Range("N2:N250"), 0), 1).value = ContactNo

This brings up:
Run-time '424'
Object required!
New idea:
Dim BED as Object
Dim Sleep as variant

lol

 

[jsotola]

this is just a starting point to solving your issue

small steps ....

can you do the formula in a cell somewhere on the worksheet so that the correct values are returned on the worksheet

put the parameter cells near that cell



then copy the formula and post it here

also post the parameter values

 

[Scorpion Steve]

Ahh I can but Im using a userform to update the fields within the spread sheet.

 

[Scorpion Steve]

Ppl - Figured it out!
My fault 1stly - I had the ranges the wrong way around so would have mucked up anyways.
I managed it from:

Application.Index(Sheets("DATA SHEET").Range("ContactNoRange"), Application.Match(SearchInput, (Sheets("DATA SHEET").Range("PCIDRange")), 0)) = ContactNo

Thanks to all. It actually helps ppl taking a stab in getting ideas working!

Steve