Reference to use index formula and name in VBA Excel

Question

I want to ask, how about the code in writing VBA Excel like this.

Textbox1=Application.WorksheetFunction.Index(dataname, Application.WorksheetFunction.Match(SpinButton1.Value, indexdataname, 0), 3)

In simple formulas on sheets like

=INDEX(dataname,MATCH(number,indexdataname,0),3)

What is indexdataname? It is not defined. Same with dataname. Please show a minimal reproducible example. — Pᴇʜ
– Pᴇʜ, Commented Jul 31, 2019 at 11:33
dataname and indexdataname from define name. dataname A1: C15, indexdataname A1: A15 — borneo
– borneo, Commented Jul 31, 2019 at 11:36

Pᴇʜ · Accepted Answer · 2019-07-31 11:40:08Z

1

I assume indexdataname and dataname are named ranges.

It would work with something like below:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'define sheet name

Dim Dataname As Range
Set Dataname = ws.Range("dataname")

Dim IndexDataname As Range
Set IndexDataname = ws.Range("indexdataname")

Dim MatchedRow As Double
On Error Resume Next 'next line throws error if no match is found
MatchedRow = Application.WorksheetFunction.Match(SpinButton1.Value, IndexDataname, 0)
On Error Goto 0 'always re-activate error reporting !!!

'test if something matched
If MatchedRow = 0 Then
    MsgBox "Nothing matched", vbCritical
    Exit Sub
End If

Textbox1 = Application.WorksheetFunction.Index(Dataname, MatchedRow, 9)

answered Jul 31, 2019 at 11:40

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

borneo Over a year ago

Your explanation is very perfect, complete with comments and very helpful for me who is still a layman studying excel. Once again, thank you

Collectives™ on Stack Overflow

Reference to use index formula and name in VBA Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related