find into array without loop

[sal21]

M;y pece of code:

Code: Select all

into the top of module:
Dim strDBRows() As Variant
....
..........
SQL = "SELECT ABICODE, DESCRIZIONE, CABCODE FROM CAB ORDER BY ABICODE, CABCODE"
    Set RS = New ADODB.Recordset
    RS.Open SQL, CONN, adOpenStatic, adLockReadOnly, adCmdText

    Y = RS.RecordCount
    Erase strDBRows()
    strDBRows = RS.GetRows(Y)
    RS.Close
    Set RS = Nothing
...........
    Call StringAlong
............
Sub StringAlong()

    Dim Sample As String

    Sample = "01005"
    If UBound(Filter(strDBRows, Sample)) >= 0 Then
        MsgBox ("found it")
    Else
        MsgBox ("did not find it")
    End If
End Sub

Sample = "01005" is a possible value into the first column of array

i need to chek if exists and return the postion number of founded value

have error rintime 13 in:
If UBound(Filter(strDBRows, Sample)) >= 0 Then

note;
the array can contain approx 2.500.000 items and i dont want to ceckh the value with a for next loop

[HansV]

The Filter function can only be used for a one-dimensional array of strings, not for strDBRows, a two-dimensional array that represents records.

I'd open a recordset to find the value:

Code: Select all

    SQL = "SELECT ABICODE FROM CAB WHERE ABICODE Like '01005'"
    RS.Open SQL, CONN, adOpenStatic, adLockReadOnly, adCmdText
    If RS.EOF And RS.BOF Then
        MsgBox "Not found"
    Else
       MsgBox "Found"
    End If
    RS.Close

or

Code: Select all

    SQL = "SELECT Count(ABICODE) FROM CAB WHERE ABICODE Like '01005'"
    RS.Open SQL, CONN, adOpenStatic, adLockReadOnly, adCmdText
    If RS.Fields(0) = 0 Then
        MsgBox "Not found"
    Else
       MsgBox "Found"
    End If
    RS.Close

[sal21]

The Filter function can only be used for a one-dimensional array of strings, not for strDBRows, a two-dimensional array that represents records.

I'd open a recordset to find the value:

Code: Select all

    SQL = "SELECT ABICODE FROM CAB WHERE ABICODE Like '01005'"
    RS.Open SQL, CONN, adOpenStatic, adLockReadOnly, adCmdText
    If RS.EOF And RS.BOF Then
        MsgBox "Not found"
    Else
       MsgBox "Found"
    End If
    RS.Close

or

Code: Select all

    SQL = "SELECT Count(ABICODE) FROM CAB WHERE ABICODE Like '01005'"
    RS.Open SQL, CONN, adOpenStatic, adLockReadOnly, adCmdText
    If RS.Fields(0) = 0 Then
        MsgBox "Not found"
    Else
       MsgBox "Found"
    End If
    RS.Close

Baseed strDBRows and strDBRows1 (with some structure of data) how to match the key#1 of first array with tke key1 of the second array? If the key#1 is forund in the second array msgnox" found in position #

[HansV]

You'd have to write a double loop; that's very inefficient. It'd be much better to match the data in SQL.