0

I have been following this question quite deeply and trying to match Cell(r,2) on one sheet to an array I compiled below cycling through rows in column 2 on another sheet but I keep getting a False value returned from the function. 

    Public Break_List(1 To 1000, 1 To 100) As Variant

    If IsInArray(Cells(r, 2), Break_List) = True Then

    Sub Store_Break_Categories()
    Sheets("BackEnd").Select
    Break_No_of_Rows = 0
    'For c = 10 To 15
    counter = 0
        If Cells(2, 3) <> "" Then
        lastrow = Cells(65000, 3).End(xlUp).Row
           For r = 2 To lastrow
              counter = counter + 1
               'Break_List(counter, c - 9) = Cells(r, c)
                  Break_List(counter, 1) = Cells(r, 3)
           Next r
        End If
        If counter > Break_No_of_Rows Then Break_No_of_Rows = counte
    End Sub

This is the function I have integrated from the above question

    Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
        IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
    End Function

Thank you

braX
  • 11,506
  • 5
  • 20
  • 33
phillipsK
  • 1,466
  • 5
  • 29
  • 43
  • ?? what you've posted isn't valid code. You'll need to provide sample data and expected results as well, along with what you're currently seeing *instead* of expected results. – tigeravatar Oct 24 '17 at 17:06
  • I don't think that function will work on a 2D array. You will have to loop through each each column. You can use Application.Index to shortcut https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html – SJR Oct 24 '17 at 17:06

1 Answers1

1

The Application.Match is not going to magically look through 100 columns. If you want to look through the first column then,

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0))
End Function

If you want to look through all columns then,

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    dim a as long
    IsInArray = false
    for a = lbound(arr, 2) to ubound(arr, 2)
        If Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, a), 0)) then
            IsInArray = true
            exit function
        end if
    next a
End Function