Using Named Ranges with INDEX MATCH in VBA

[sparky2205]

Hi folks,
I have a small piece of code that changes the text of a button depending on what is selected from a drop down list.
The drop down list is on a worksheet called Tabelle2
Both the INDEX range and the MATCH range are on a worksheet called Languages
The code below works fine but I want to change it to...
k = Application.Index(Sheets("Languages").Range(Button), Application.Match(i, Sheets("Languages").Range(Index), 0))
...as the ranges are dynamic.
But this throws the "Application-defined or object-defined error"
Maybe I'm not referencing these ranges correctly?

Private Sub Worksheet_Change(ByVal Target As Range)
     
     Dim i As Integer
     Dim k As String
     
     i = ActiveSheet.Range("D1").Value
     
     k = Application.Index(Sheets("Languages").Range("B2:B4"), Application.Match(i, Sheets("Languages").Range("A2:A4"), 0))
     
     ActiveSheet.Shapes("Button 1").TextFrame.Characters.TEXT = k

End Sub

 

[maabadi]

Match isn't a VBA Function then change k to :

k = Application.Index(Sheets("Languages").Range("B2:B4"), Application.Worksheetfunction.Match(i, Sheets("Languages").Range("A2:A4"), 0))

 

[sparky2205]

I've changed that to:
k = Application.WorksheetFunction.Index(Sheets("Languages").Range("B2:B4"), Application.WorksheetFunction.Match(i, Sheets("Languages").Range("A2:A4"), 0))
to match what you've said above but that wasn't the problem as that line you've quoted above was already working.

This is the actual problem. When I change that line to refer to named ranges instead of explicitly stating the ranges it doesn't work:
k = Application.WorksheetFunction.Index(Sheets("Languages").Range(Button), Application.WorksheetFunction.Match(i, Sheets("Languages").Range(Index), 0))

Button and Index are dynamic ranges currently referring to B2:B4 and A2:A4 respectively. They are located on another worksheet called Languages.
When I change from stating the range B2:B4 to the named range Button and range A2:A4 to the named range Index I get the "Application-defined or object-defined error"

 

[Alex Blakenburg]

If you mean that they are both Named Ranges in the Workbook, try adding quotation marks around both range names
eg

k = Application.Index(Sheets("Languages").Range("Button"), _
            Application.Match(i, Sheets("Languages").Range("Index"), 0))

 

[sparky2205]

Strange, I had already tried that and it didn't work. But now it works perfectly.
I obviously had something else changed in the code that was causing a problem at the time.
Regardless it's sorted now and thanks very much for your help.

I had been trying different things earlier this morning and had come up with another solution, by using the last used row to dynamically set the upper limit of the range.
Even though this is not as versatile as using the named ranges directly I thought I'd post it here anyway in case someone else might find it useful.

Thanks again for your help and have a good weekend.

I did have another issue which I posted in the last few days but I don't want to cross post as that is a different topic.
I feel cheeky enough even mentioning it here.
But if you did have some time I'm sure you'd find it by searching my posts under my username. It's an issue that I'm finding impossible to resolve and it's really holding up my work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
     Dim k As String
     Dim r As Integer
     
     i = ActiveSheet.Range("D1").Value
     r = Worksheets("Languages").Cells(Rows.Count, "A").End(xlUp).Row

     k = Application.WorksheetFunction.Index(Sheets("Languages").Range("B2:B" & r), Application.WorksheetFunction.Match(i, Sheets("Languages").Range("A2:A" & r), 0))
     ActiveSheet.Shapes("Button 1").TextFrame.Characters.TEXT = k
          
End Sub

 

[Alex Blakenburg]

Thanks for your feedback and for sharing. The xlup method is actually more commonly used in code than using a Named Dynamic range.
PS: I haven't done much in terms of inserting pictures (which I assume you are referring to) but I will have a look.