参考使用索引公式和vba Excel中的名称

Question

我想问，以下书面vba excel中的代码怎么样。

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

在

之类的简单公式中

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

Answer 1

我假设 indexdataname和 dataname被命名为范围。

它将与下面的类似：

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)