CODEWITHSUNDEEP
arraysexcelvba
Alastair
Alastair

Reputation: 83

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

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.

Code with Array

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

Upvotes: 0

Views: 105

Answers (1)

BigBen
BigBen

Reputation: 49998

# 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.

Upvotes: 1

Related Questions