VBA中的指数匹配范围（根据贷款编号和日期查找贷款余额） [英] Index Match in VBA for Ranges (finding a loan balance given a loan number and a date)

问题描述

我试图在VBA中实现索引匹配组合，以找到给定2个条件的范围内的数字。下面似乎是一个很好的方法，但是，我的输入不是来自excel，而是来自代码本身发生变化的变量。对于我的生活，我无法理解，但我是一个新手。

I was trying to implement the index match combination in VBA to find a number in a range given 2 conditions. The below seems like a great approach, however, my inputs do not come from excel but from a variable that changes in the code itself. For the life of me I can't figure it out but I am a newbie.

Excel / VBA - 使用动态范围的索引匹配功能

如果您的情况会怎样？名称和日期是贷款号码（1,2,3等）和日期（2013年6月30日），不在电子表格中，而是在VBA代码中生成，以便代码可以转到范围和在这样的日期查找该贷款的余额并将其存储到变量

What happens if your name and date instead are a loan number (1,2,3,etc) and date (6/30/2013) and are not in a spreadsheet but are generated in the VBA Code so that then the code can go to a range and look for the balance of that loan in such date and store it to a variable

-----------------范围定义-------------------------------------------------- -----------------------

-----------------RANGE DEFINITIONS-------------------------------------------------------------------------

关于代码：Cantidad，ID和Fecha是以下列方式定义的动态范围：

With Worksheets("CFs")
Set ID = Range("offset($a$3,4,0,counta($A:$A)-4,1)")
Set Fecha = Range("offset($b$3,4,0,counta($B:$B)-4,1)")
Set Cantidad = Range("offset($f$3,4,0,counta($F:$F)-4,1)")
End With

- ----------------功能代码-------------------------------- --------------------------------------
关于函数：dia1和ID是一个每月更改的日期和一个循环一次的贷款号，直到达到贷款总数。

------------------FUNCTION CODE---------------------------------------------------------------------- about the function : dia1 and ID are a date that changes monthly and a loan number that loops one a time until the total number of loans are reached.

Public Function TestIndexMatch1(ByRef Cantidad As Range, _
                                                    ByRef Prestamo As Integer, _
                                                    ByRef Dia1 As Date, _
                                                    ByRef ID As Range, _
                                                    ByRef Fecha As Range)

                    Const Template As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4},{5}))"

                    Const MATCH_TYPE = 0
                    On Error GoTo Err_Handler
                    Err.Number = 0

                    Dim originalReferenceStyle
                    originalReferenceStyle = Application.ReferenceStyle
                    Application.ReferenceStyle = xlR1C1

                    Dim myFormula As String
                    myFormula = Replace(Template, "{0}", Cantidad.Address())
                    myFormula = Replace(Template, "{1}", Prestamo.Address())
                    myFormula = Replace(Template, "{2}", Dia1.Address())
                    myFormula = Replace(Template, "{3}", ID.Address())
                    myFormula = Replace(Template, "{4}", Fecha.Address())

                    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
                        If (Err.Number <> 0) Then MsgBox Err.Description
                        Application.ReferenceStyle = originalReferenceStyle


End Function

推荐答案

首先看起来你似乎缺少一些东西：

First off it looks like you are missing a few things:

1. 公式字符串中的右括号... {4} ），{5} ...


2. 名称定义中范围之前的点（设置ID = .Range）

1. a closing bracket in the formula string ...{4}),{5}...
2. a dot before range in your name definitions (Set ID = .Range)

但我认为你可能想要稍微改变一下。如果要在工作表函数中定义要在工作表函数中使用的VBA中的变量，则需要注意定义依赖关系。 VBA函数中使用的每个范围都应该是函数的输入参数，因此只要该值发生更改，Excel就会重新计算。在代码中评估公式的另一个问题是缺少中间调试信息，并且函数并不总是像在工作表上那样进行评估。

But i think you might want to go about things a little differently. If you are defining variables in VBA to be used in worksheet functions on the sheet, you need to be careful about defining dependencies. Each range that is used in the VBA function should be an input argument to the function so Excel will recalculate whenever that value changes. Another issue with evaluating formulas in code is there is a lack of intermediate debug information and functions don't always evaluate as they would on the sheet.

第一部分我您可以考虑设置工作表名称，这可以通过对上面的代码进行以下调整来完成：

For the first part I think you want to setup sheet names, this can be done by making the following adjustment to your code above:

Sub SetUpNames()
With Worksheets("CFs").Names
    .Add "ID", "=offset($a$3,4,0,counta($A:$A)-4,1)"
    .Add "Fecha", "=offset($b$3,4,0,counta($B:$B)-4,1)"
    .Add "Cantidad", "=offset($f$3,4,0,counta($F:$F)-4,1)"
End With
End Sub

这也可以通过定义的名称对话框完成。然后可以将这些名称插入到链接帖子中的函数中。

This could also be done through the defined name dialog box. These names can then be inserted into the function in the linked post.

对于第二部分代码，这里是链接帖子中的函数的替代方法，它也使用工作表函数方法：

For the second section of code, here is an alternative to the function in the linked post which also uses a worksheet function approach:

Public Function TestIndexMatch2(ByRef outputRange As Range, _
                            ByRef nameCriteria As Range, _
                            ByRef dateCriteria As Range, _
                            ByRef nameRange As Range, _
                            ByRef dateRange As Range)

Dim v as Variant
With Application
    v = .CountIfs(nameCriteria, nameRange, dateCriteria, dateRange)
    TestIndexMatch2 = .Index(outputRange, .Match(1, v, 0))
End With

End Function

（现在，如果需要，至少可以使用监视窗口来评估中间结果）。

(Now at least the watch window is available to evaluate intermediate results if required).

注意：在没有.WorksheetFunction的情况下使用Application会返回一个变量，它允许参数和结果中的数组。但是，VBA不公开所有工作表函数或Excel运算符，因此您需要使用此方法更加足智多谋，例如 Exact 函数缺失但是有一些变通方法（替换（a，b， ））= 0。

Note: Using Application without .WorksheetFunction returns a variant which allows for arrays in arguments and results. However VBA doesn't expose all worksheet functions or Excel operators so you need to be more resourceful with this approach, for example the Exact function is missing but there are workarounds like len(substitute(a,b,""))=0.

这篇关于VBA中的指数匹配范围（根据贷款编号和日期查找贷款余额）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！