INDEX - 有错误的MATCH公式？ [英] INDEX - MATCH formula with errors?

问题描述

我有excel公式：

=INDEX('C:\Users\Desktop\[BOOK1.xlsx]Sheet1'!$J:$J,MATCH(A2,'C:\Users\Desktop\[BOOK1.xlsx]Sheet1'!$W:$W,0))

我在Excel vba中写这个问题，这是我到目前为止所做的：

I am having trouble writing that in Excel vba, this is what I have so far:

Dim BOOK1 As Workbook
Dim bcklog1 As Worksheet

Set bcklog1 = BOOK1.Worksheets("backlog1")

Dim result As Variant
Dim match_formula As Variant

match_formula = "Match(Worksheets(1).Range("W:W"), 0)"
result = Evaluate(match_formula)
answer = Application.WorksheetFunction.Match(2, Worksheets(1).Range("W:W"), 0)
test = Application.WorksheetFunction.Index(Sheets("backlog1").Range("J:J"), result, 1)
index_formula = "Index(sales, result, )"
result2 = Evaluate(index_formula)

我无法获得任何结果，并且错误不断出现问题用我的比赛公式。我的方法是否正确？某些结果将包含错误，这是预期的。公式应该在另一个工作簿中查找值并匹配它们。

I am having trouble getting any sort of result, and an error keeps popping up in regards with my match formula. Is my approach even correct? Some results will contain an error, which is expected. The formula should lookup values within another workbook and match them.

推荐答案

目标：在VBA中重现工作工作表公式

Objective: reproduce a working worksheet formula in VBA

=INDEX('C:\Users\Desktop\[BOOK1.xlsx]Sheet1'!$J:$J, MATCH(A2, 'C:\Users\Desktop\[BOOK1.xlsx]Sheet1'!$W:$W,0))

这是使用多种方法的渐进式构造。检查VBE的立即窗口中的 debug.print 输出。

Here is a progressive construction using multiple methods. Check the VBE's Immediate window for the debug.print output.

'assumes that BOOK1 points to a workbook at this point
Dim bcklog1 As Worksheet
Set bcklog1 = BOOK1.Worksheets("backlog1")

Dim result As Variant, test As Variant
Dim frml As String, match_row As Long

'first we construct and evaluate the MATCH portion
frml = "match(A2, " & bcklog1.Range("W:W").Address(external:=True) & ", 0)"
Debug.Print frml
match_row = Evaluate(frml)
Debug.Print match_row

'next we build the remainder of the INDEX/MATCH around the match potion constructed earlier
frml = "index(" & bcklog1.Range("J:J").Address(external:=True) & ", " & frml & ")"
Debug.Print frml
result = Evaluate(frml)

'finally we abandon the Evaluate and use the match_row obtained earlier directly
test = Application.WorksheetFunction.Index(bcklog1.Range("J:J"), match_row, 1)
Debug.Print test

外部工作簿单元格范围地址作为字符串引入使用可选<$ c $的 Range.Address属性 c> external：= True 参数。这将返回格式正确的字符串，包括工作簿的完整路径，工作表名称（如果需要，带有单引号）和绝对单元格范围引用。

The external workbook cell range addresses are brought in as strings with the Range.Address property with the optional external:=True parameter. This returns a properly formatted string including the full path to the workbook, the worksheet name with single quotes if required and absolute cell range references.

您可以编写这些公式字符串结构返回到目标工作表，通过使用等号前缀并将它们分配给目标单元格 Range.Formula属性。等号不是 Application.Evaluate方法的要求。 。

You could write those formula string constructions back to the destination worksheet by prefacing with an equals sign and assigning them to the destination cell's Range.Formula property. The equals sign is not a requirement for the Application.Evaluate method.

这篇关于INDEX - 有错误的MATCH公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！