Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Find an item in an array (Excel 97 VBA)

    The following simple lookup routine doesn't work
    It tries to find target t in an sorted array a, or the place where it should be.
    i=1
    while (i<=n) and (a(i)<t)
    i=i+1
    wend
    The array check is evaluated before the 'and' operator.
    A function call has the same problem.
    And, believe it or not, ...
    ... a(Min(i,n))<t
    won't work because the function 'Min' does not exist.

    Of course I wrote my own Function Min(i1,i2) now.
    Does anybody has a more elegant solution.

  2. #2
    Join Date
    Oct 2003
    Posts
    2
    I alway use this algorithm:

    Dim fFound as Boolean

    fFound = false
    i = 1
    while (i <= n) and not fFound
    fFound = a(i) = t
    i = i + 1
    wend

    Anybody has another algorithm ?

  3. #3
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi guys,

    In XL we can use a total different approach then what we usually use in VB/VB.Net:

    Code:
    Option Explicit
    
    Sub LoopUp_Array()
    Dim vaList As Variant, vaValue As Variant, vaOutput As Variant
    
    vaList = VBA.Array("A", "B", "C", "D", "E", "F", "G")
    vaValue = "D"
    
    vaOutput = _
    Application.VLookup(vaValue, Application.Transpose(vaList), 1, 0)
    
    If Not IsError(vaOutput) Then
          MsgBox "The value " & vaValue & " exist!"
    Else
         MsgBox "The value " & vaValue & " does not exist!"
    End If
    End Sub
    Kind regards,
    Dennis
    Kind regards,
    Dennis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •