Is there a way to search within a string in a VBA array?

Question

I'm trying to find a way to search for a string inside of each element of an array once it has been copied in.

The string is definitely in some of the array elements, however, with my current code it is never found.

Any help with solving this would be much appreciated!

I have copied all of my code here at the bottom, though some of it is currently placeholder

    Sub ClassFinder()
    
    Dim LRowA, LRowG, LRowX As Integer
    Dim Period, Teacher As String
    Dim RowA, ColA, site As Integer
    Dim CellA, lftBrack, rgtBrack, Class, Room As String
    Dim FTeachers(), TeachNames(), arr() As String
    
    LRowA = Cells(Rows.Count, "A").End(xlUp).Row
    LRowG = Cells(Rows.Count, "G").End(xlUp).Row
    
    If LRowA <> LRowG Then
        Period = WorksheetFunction.Proper(Cells(LRowA, 3).Value2) & ":" & Cells(LRowA, 4).Value2
        Teacher = Cells(LRowA, 1).Value2
        RowA = Sheets("Staff Timetables").Range("A:A").Find(Teacher).Row
        ColA = Sheets("Staff Timetables").Range("A1:ZZ1").Find(Period).Column
        
        CellA = Sheets("Staff Timetables").Cells(RowA, ColA).Value2
        lftBrack = InStr(CellA, "(")
        rgtBrack = InStr(CellA, ")")
        
        Class = Left(CellA, 3)
        Room = Mid(CellA, lftBrack + 2, rgtBrack - lftBrack - 2)
        
        If Room = "A*" Or Room = "B0*" Or Room = "B1*" Or Room = "OCK2*" Or Room = "DO*" Then
            site = 2
        Else:
            site = 1
    End If
        
    LRowX = Sheets("Staff Timetables").Cells(Rows.Count, ColA).End(xlUp).Row
    TeachNames = Range(Sheets("Staff Timetables").Cells(1, 1), Sheets("Staff Timetables").Cells(LRowX, 1)).Value2
        
    If ColA < 11 Then
        FTeacher = Range(Sheets("Staff Timetables").Cells(1, 2), Sheets("Staff Timetables").Cells(LRowX, 9)).Value2
    ElseIf ColA < 20 Then
        FTeacher = Range(Sheets("Staff Timetables").Cells(, 11), Sheets("Staff Timetables").Cells(LRowX, 19)).Value2
    ElseIf ColA < 29 Then
        FTeacher = Range(Sheets("Staff Timetables").Cells(1, 20), Sheets("Staff Timetables").Cells(LRowX, 28)).Value2
    ElseIf ColA < 38 Then
        FTeacher = Range(Sheets("Staff Timetables").Cells(1, 29), Sheets("Staff Timetables").Cells(LRowX, 37)).Value2
    ElseIf ColA < 47 Then
        FTeacher = Range(Sheets("Staff Timetables").Cells(1, 38), Sheets("Staff Timetables").Cells(LRowX, 46)).Value2
    End If
    
    For i = LBound(FTeacher) To UBound(FTeacher)
        For j = LBound(FTeacher, 2) To UBound(FTeacher, 2)
            If FTeacher(i, j) Like ("*(#" & Room & ")") Then
                GoTo x
    x:
            
            End If
        Next j
    Next i
        
    End If
    
    End Sub

BigBen · Accepted Answer · 2020-09-15 12:51:40Z

1

# is a special character matching any single digit.

If you want to match # literally, you need to enclose it in brackets. Also note that you've got an extra set of outer parentheses (outside the quotes) that should be dropped.

If FTeacher(i, j) Like "*([#]" & Room & ")" Then

See the Like docs for more detail.

edited Sep 15, 2020 at 12:51

answered Sep 15, 2020 at 12:48

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there a way to search within a string in a VBA array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related