i have an array and after it gets populated im trying to check if it contains a certain text. the array and certain text are dynamic and will be different every time i run the code. i found a function code that looks like it should work but i get a 'type mismatch' error when it runs through the function
as below
Sub newtest()
Dim today As Date
Dim ystrday As Date
Dim twodays As Date
Dim top As String
Dim bot As String
Dim home As String
Dim away As String
home = Workbooks("CHL Schedule.xlsm").Sheets(3).Range("W2")
away = Workbooks("CHL Schedule.xlsm").Sheets(3).Range("V2")
today = Workbooks("CHL Schedule.xlsm").Sheets(1).Range("B404").End(xlUp).Offset(1, -1)
If today = "01/10/2002" Then
ystrday = today
twodays = today
Else
If today = "02/10/2002" Then
ystrday = today - 1
twodays = today
Else
ystrday = today - 1
twodays = today - 2
End If
End If
top = Workbooks("CHL Schedule.xlsm").Sheets(1).Range("A:A").Find(today).Address(False, False)
bot = Workbooks("CHL Schedule.xlsm").Sheets(1).Range("A:A").Find(today, after:=Cells(3, 1), searchDirection:=xlPrevious).Address(False, False)
Dim Arrtoday() As Variant
Arrtoday = Range(Workbooks("CHL Schedule.xlsm").Sheets(1).Range(top).Offset(0, 1), Workbooks("CHL Schedule.xlsm").Sheets(1).Range(bot).Offset(0, 2))
Dim R As Long
Dim C As Long
For R = 1 To UBound(Arrtoday, 1)
For C = 1 To UBound(Arrtoday, 2)
Debug.Print Arrtoday(R, C)
Next C
Next R
If IsInArray(home, Arrtoday) or IsInArray(away, Arrtoday) Then
'some code
Else
'some more code
End If
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
i get the error code on this line:
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
all i want to do is check to see if the array contains what home or away is and depending on if it is or not 2 different things will happen
if anyone can see where im going wrong or if im missing something any help would be appreciated.
thanks
Bookmarks