vba 如何在作为参数传递给函数的范围中查找最小和最大行和列

How to find minimum and maximum row and column in a range that is passed as a parameter into a function

提问by Peekay

I am trying to write special VLookup function in VBA where instead of one value that is looked up in a column range, multiple values are looked up in multiple columns ranges. When all the "lookup" values match the respective values in the columns, a value that is few specified columns away is returned. Something like

我正在尝试在 VBA 中编写特殊的 VLookup 函数，而不是在列范围中查找一个值，而是在多个列范围中查找多个值。当所有“查找”值与列中的相应值匹配时，将返回与指定列相距很少的值。就像是

splvlookup (RngArea As Range, OptionName As String, StrikePrice As Single, TrdDate As Date, dcr As Integer, Columnref As Integer)

splvlookup（RngArea 作为范围、OptionName 作为字符串、StrikePrice 作为单个、TrdDate 作为日期、dcr 作为整数、Columnref 作为整数）

where

在哪里

RngArea is the range of the total table

RngArea 是总表的范围

Name, Price, Dt are values that are looked up in their respective columns in RngArea (Column numbers are not passed as parameters into the function as this is not necessary, the column headers do not change and all the columns are arranged in the same order always)

Name、Price、Dt 是在 RngArea 中各自的列中查找的值（列号不会作为参数传递给函数，因为这不是必需的，列标题不会改变，所有列都以相同的顺序排列总是）

dcr is a decrement counter, this decrements the date by a few days. Thus, date which is looked up will be Dt -dcr

dcr 是一个递减计数器，它将日期递减几天。因此，查找的日期将是 Dt -dcr

Columnref is a reference number which is the number of the columns from the first column in RngArea

Columnref 是一个参考编号，它是 RngArea 中第一列的列数

In my table RngArea, there are multiple values for each of the variables Dt, Price, Name. However, there is only one unique combination of all three put together. The task is to find that unique row and find the corresponding value in the column identified by columnref.

在我的表 RngArea 中，每个变量 Dt、Price、Name 都有多个值。然而，这三者只有一种独特的组合。任务是找到该唯一行并在 columnref 标识的列中找到相应的值。

My Question

我的问题

How to find the minimum and maximum row and column ranges. Once I find this, I can use loops within these min and max ranges and check for the lookup values. I am sure this is a very simple trick for a pro but seems to be beyond me. Any help appreciated. Thanks in advance.

如何找到最小和最大行和列范围。一旦我找到了这个，我就可以在这些最小和最大范围内使用循环并检查查找值。我相信这对于专业人士来说是一个非常简单的技巧，但似乎超出了我的范围。任何帮助表示赞赏。提前致谢。

Here is the code I used based on PA's advice, the code is fixed.

这是我根据 PA 的建议使用的代码，代码是固定的。

Function splVlookup(RngArea As Range, OptionName As String, StrikePrice As Single, TrdDate As Date, Datedecrement As Integer, Columnref As Integer) As Variant
Dim i As Long

i = 1

Do While i < RngArea.Rows.Count + 1

If (RngArea.Cells(i, 9) = StrikePrice) And (RngArea.Cells(i, 3) = OptionName) And (RngArea.Cells(i, 1) = (TrdDate - Datedecrement)) Then

splVlookup = RngArea.Cells(i, Columnref)
Exit Function
End If

i = i+1

Loop

If i = RngArea.Rows.Count + 1 Then
splVlookup = CVErr(xlErrNA)
End If

End Function

Table structure is like this (delimited by '|')

表结构是这样的（以'|'分隔）

TrdDate | Option |OptionName|Call Volume|Call Open.Int|Call Bid|Call Ask|Exp Date|StrikePrice|Put Bid|Put Ask|Put Volume|Put Open.Int

日期 | Option |OptionName|Call Volume|Call Open.Int|Call Bid|Call Ask|Exp Date|StrikePrice|Put Bid|Put Ask|Put Volume|Put Open.Int

采纳答案by PA.

Given a Range, you can access its .Rows.Countand .Columns.Countand use simple Forloops to iterate over its .Cellsproperty.

给定 a Range，您可以访问它的.Rows.Countand.Columns.Count并使用简单的For循环来迭代它的.Cells属性。

For i = 1 To rng.Rows.Count
  For j = 1 To rng.Columns.Count
    set Val = rng.Cells(i, j).Value
  Next j
Next i

try this code with different ranges.

尝试使用不同范围的代码。

You will notice that .Cellsrefers to the cells contained in the Range relative to its origin. So, for Range("B2:C4"), its .Cells(1,2)will refer to "C2".

您会注意到，它.Cells指的是相对于其原点的 Range 中包含的单元格。因此，对于Range("B2:C4")，其.Cells(1,2)将指代"C2"。

It also copes with Ranges that contain non-contiguous cells. For Range("B2:C4","D2:E4"), its .Cells(1,3)will refer to "D2".

它还处理包含非连续单元格的范围。对于Range("B2:C4","D2:E4")，其.Cells(1,3)将参考"D2"。

回答by B Hart

If you are just looking for the first and last cells in a range you can try something like the below:

如果您只是在寻找范围内的第一个和最后一个单元格，您可以尝试如下操作：

Dim RR As Range
Set RR = Range("B2:D7")
MsgBox "First Cell: " & RR(1).Address & vbCrLf & _
    "Last Cell: " & RR(RR.Count).Address

'You can also use RR(#).Row or RR(#).Column if you need those values instead

If your trying to search for a Value within a Range take a look at the below routine and see if you can modify your code...

如果您尝试在范围内搜索值，请查看以下例程，看看您是否可以修改代码...

Sub test()
    MsgBox FindValue(Sheet1.Range("B2:D7"), "X")
End Sub

Function FindValue(RngArea As Range, strSearch As String)
Dim rCL As Range
Dim sFirstAddress As String
Dim rRES As Range

Set rCL = Nothing
Set rRES = Nothing
Set rCL = RngArea.Find(strSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
If rCL Is Nothing Then FindValue = CVErr(xlErrNA)
sFirstAddress = rCL.Address
Do
    If rRES Is Nothing Then
        Set rRES = rCL
    Else
        Set rRES = Application.Union(rRES, rCL)
    End If
    Set rCL = RngArea.FindNext(rCL)
Loop While Not rCL Is Nothing And rCL.Address <> sFirstAddress

FindValue = rRES.Address
End Function

回答by jpatters

Use Intersect and do a binary search:

使用 Intersect 并进行二分查找：

Option Explicit

Public Const WS_MAX_ROWS = 1048576
Public Const WS_MAX_COLUMNS = 16384

Public Function rngMinRow(rng As Range) As Long
'   return the minimum row number within the extent of rng
'
    Dim lrow As Long
    Dim mrow As Long
    Dim hrow As Long
    Dim found As Boolean
    Dim return_value As Long

    lrow = 1
    mrow = WS_MAX_ROWS \ 2
    hrow = WS_MAX_ROWS
    found = False
    return_value = -1

    If Not rng Is Nothing Then
        Do While Not found
            If Not Intersect(Cells.Rows(lrow).Resize(mrow - lrow + 1), rng) Is Nothing Then
                If lrow = mrow Then
                    return_value = lrow
                    found = True
                Else
                    hrow = mrow
                    mrow = (lrow + hrow) \ 2
                End If
            Else
                If hrow - mrow = 1 Then
                    return_value = hrow
                    found = True
                Else
                    lrow = mrow + 1
                    mrow = (lrow + hrow) \ 2
                End If
            End If
        Loop
    End If

    rngMinRow = return_value
End Function

The above should work for any arbitrary range, and it does not iterate through the entire range nor does it use cells.count which can raise an error if there are more cells than the max value for a long integer. This should be pretty easy to adapt for rngMaxRow, rngMinColumn, and rngMaxColumn.

以上应该适用于任何任意范围，并且它不会遍历整个范围，也不会使用 cell.count 如果单元格数量超过长整数的最大值，则可能会引发错误。这应该很容易适应 rngMaxRow、rngMinColumn 和 rngMaxColumn。