CODEWITHSUNDEEP
excelvba
Tinus Jackson
Tinus Jackson

Reputation: 3653

Find value in Excel (VB)

i am struggling to get my vb script to work.

I need to find value a(string) and b(date) in an array.

Example:

[string1][date1] where in arr[[st1][dat1],[st2][dat2],[string1][date1]]

The last one will match true the first and second will be false

Values:

Sheet 1: Sheet 1

Sheet 2: Sheet 2 my code:

Function IsInArray(stringToBeFound1 As String, ByVal dateToBefound As Date, arr As Variant) As Boolean
   Dim Found1 As Boolean
   Dim Found2 As Boolean
   Dim x As Integer
   
   Found1 = False
   Found2 = False
   x = 0
   
    For x = 0 To UBound(arr, 1) // this does not seem to work
        If stringToBeFound1 = arr(x, 0) Then
            Found1 = True
        End If
        If dateToBefound = arr(x, 1) Then
            Found2 = True
        End If
        If Found1 = 1 And Found2 = 1 Then IsInArray = True
    Next

End Function

usage: =IsInArray(C6,D6,'Sheet2'!C:D)

Upvotes: 0

Views: 63

Answers (1)

Variatus
Variatus

Reputation: 14373

The key fault of your code is that the parameter you are giving to the function isn't an array. It's a range. Please read more comments in the code below.

Function IsInArray(stringToBeFound As String, _
                   ByVal dateToBefound As Date, _
                   Rng As Range) As Boolean
    ' =IsInArray(C6,D6,'Sheet2'!C:D)        ' C:D is a range, not an array

    Dim MyRng   As Range
    Dim Arr     As Variant
    Dim R       As Long             ' loop counter: rows
   ' since there are more than a million rows in a sheet
   ' and an integer can only count up to abt 32000
   ' you can't use an Integer to loop through all rows
   
'   Found1 = False          ' }
'   Found2 = False          ' } These values are given by the Dim statements above
'   x = 0                   ' }

    ' all the column is too big a range.
    ' it will take ages to find nothing.
    ' Exclude blank cells at the bottom:-
    With Rng
        Set MyRng = Range(.Cells(1), Cells(Rows.Count, .Column).End(xlUp)) _
                    .Resize(, .Columns.Count)
'        ' you may prefer this one, actually:
'        Set MyRng = Range(.Cells(2, 1), Cells(Rows.Count, .Column).End(xlUp)) _
'                    .Resize(, .Columns.Count)
        Debug.Print MyRng.Address       ' for testing MyRng
    End With
    Arr = MyRng.Value       ' creates a 1-based 2-D array
    For R = 1 To UBound(Arr, 1)
        ' always compare the code-generated variable with the given constant
        If Arr(R, 1) = stringToBeFound Then
'        ' you may prefer a case-insensitive comparison:-
'        If StrComp(Arr(R, 1), stringToBeFound, vbTextCompare) = 0 Then
            If dateToBefound = Arr(R, 2) Then
                IsInArray = True
                Exit For
            End If
        End If
    Next
End Function

I tested the function both as a UDF, with the syntax you wanted and in VBA, using the code below. When using it as a UDF I urge you to reference the target range with an absolute address, like Sheet2'!$C:$D. Not doing so is rather like asking for trouble.

Private Sub Test()
    ' =IsInArray(C6,D6,'Sheet2'!C:D)
    Debug.Print IsInArray("B", Date + 1, Sheet2.Columns("C:D"))
End Sub

Upvotes: 2

Related Questions