Geek asks and answers

Why does my table work differently than when called from code?

When I call a function from Excel (in a cell):

=allVlookup(O24,A:D,3,"")

vs via vba

MsgBox allVlookup(Range("O24"), Range("A:D"), 3, "")

I get different results. When called from Excel, I get only the first match, but when called from the vba routine with identical parameters (with the exception of adding Rangeto the arguments that allow the routine to execute), I get full results (which is more than one).

Function Used:

Public Function allVlookup(lookupRange As Range, tableRange As Range, colIndex As Integer, Optional delimiter As String = "") As String

    Dim c As Range
    Dim firstAddress As String
    'MsgBox tableRange.Address  ' this is correct
    'With Sheets(4).Range("A1:C12").Columns(1)
    'With Range("A1:C12").Columns(1)

    'this doesn't allow things to work right either (???)
    'Set tableRange = Range("A:D")
    'Set lookupRange = Range("O24")

    'search only the first column for matches
    With tableRange.Columns(1)
        Set c = .Find(what:=lookupRange.Value, LookIn:=xlValues)

        If Not c Is Nothing Then

            firstAddress = c.Address

            Do
                'add the delimiter
                If (allVlookup <> "") Then
                    allVlookup = allVlookup + delimiter
                End If

                'append value to previous value
                allVlookup = allVlookup + c.Offset(0, colIndex).Value


                Set c = .FindNext(c)
                'exit conditions
                'no match found
                If (c Is Nothing) Then
                    Exit Do
                    'we're back to start
                ElseIf (c.Address = firstAddress) Then
                    Exit Do
                End If

            Loop
        End If
    End With

End Function

I find it difficult to explain why this is happening.

What can I do to get the same results?

+5
source share
2 answers

The reason he gives only the first match is a mistake. See Link (SECTION 5) below.

. , .

, - ( )

.FindNext , . .

, Sheet1:

A1 → Colt
A2 → Holt
A3 → Dolt
A4 → Hello


B1 → olt

, , $A$1:$A$3

Sub Test()
    Sample Sheets("Sheet1").Range("B1"), Sheets("Sheet1").Range("A1:A4")
End Sub

Sub Sample(FirstRange As Range, ListRange As Range)
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange

        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange)

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        MsgBox aCell.Address
    Else
        MsgBox "Not Found"
    End If
End Sub

, , (Say in Cell C1) =FindRange(A1,A1:A5)

1-

, , , , - $A $2!!!

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange

        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange)

            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

.

.FindNext .Find, ($ A $1: $A $3). . , :

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange

        Do While ExitLoop = False
            Set oRange = ListRange.Find(what:=FirstRange.Value, After:=oRange, LookIn:=xlValues, _
            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function
+2

. .Find :

Set c = .Find(what:=lookupRange.Value2, after:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)

.FindNext :

Set c = .Find(what:=lookupRange.Value2, after:=c, LookIn:=xlValues, LookAt:=xlWhole)

, tableRange . , , .

() . :

    |  A  |  B  |  C  |  D  |
  --+-----+-----+-----+-----+
  1 | ABC    1     2     A
  2 | ABC    3     4     B
  3 | ABC    5     6     C

ABC range("A1:D3") D : BCD. ABC .

+3

More articles:


All Articles