How to Filtering through a column's cells and put certain data in a different sheet using VBA-Excel

[fahadun]

I am working on a macro. That will select a column, search every cell of that column.
in those cells data sample are like this.

BQ43BI
BQ38AV
BQ64BH
BQ49AW
BQ51AA
BQ44BE
BQ49AA
BQ49BE
BQ52AA
BQ28BJ
BQ39Z
BQ40X
BQ61AB
BQ57AJ

Now i want to search into these strings with some classification

- BQ is fine it stays
- when the number and words are
- between 38-59 & "X" or"Y" - the entire row of that cell goes to sheet 2
- between 38-56 & "z" -the entire row of that cell goes to sheet 2
- between 28-55 & "AA"- the entire row of that cell goes to sheet 2
- Everything else goes to sheet 3, even the empty cells goes to sheet 3.

So far I have this, but working partially. It's putting all the things I want in sheet 2, but also bringing sheet 3 material, where did I go wrong?

Sub createfilter()

    Dim tiles_arr() As String
    tiles_arr = GetTiles()

    Dim bqWB As Workbook
    Set bqWB = Files.SelectFile("Select the data file")

    If bqWB Is Nothing Then
       Exit Sub
    End If

    Dim sheet As Worksheet
    Set sheet = bqWB.Sheets(1)
    lastrow = sheet.Range("A" & Rows.Count).End(xlUp).row

    Call CreateSheet(bqWB, "data sheet")
    Dim sheet2 As Worksheet
    Set sheet2 = bqWB.Sheets(2)
    
    Call CreateSheet(bqWB, "everything else")
    Dim sheet3 As Worksheet
    Set sheet3 = bqWB.Sheets(3)
    
    lastcolumn = sheet.Cells(1, sheet.Columns.Count).End(xlToLeft).Column

    Dim headers As Range
    Set headers = sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, lastcolumn))

    Dim tile_col As Integer
    tile_col = 0

    'Search for "Tile" column
    For Each header In headers
        tile_col = tile_col + 1
        header = Replace(header, " ", "")
        header = UCase(header)
        If InStr(header, "TILE") <> 0 Then
            Exit For
        End If
    Next

    j = 1
    K = 1
    For i = 2 To lastrow
        tile = sheet.Cells(i, tile_col)
        bool = False
        For Each element In tiles_arr
            If tile = element Then
                bool = True
            End If
        Next
        If bool = True Then
            sheet.Cells(i, 1).EntireRow.Copy sheet2.Cells(j, 1)
            j = j + 1
        Else
            sheet.Cells(i, 1).EntireRow.Copy sheet3.Cells(K, 1)
            K = K + 1
        End If
    Next i
    
    End Sub
    Private Sub CreateSheet(bqWB As Workbook, sheetname As String)
    Dim ws As Worksheet
    Set ws = bqWB.Sheets.Add(After:= _
             bqWB.Sheets(bqWB.Sheets.Count))
    ws.name = sheetname
    End Sub

    Function GetTiles() As String()

    Dim tiles() As String
    ReDim tiles(0 To 0) As String
    
    bq = "BQ"
    
    For i = 28 To 59
        
        If i >= 38 And i <= 59 Then
            ReDim Preserve tiles(0 To UBound(tiles) + 1) As String
            tiles(UBound(tiles)) = bq + CStr(i) + "X"
        End If
        
        If i >= 38 And i <= 59 Then
            ReDim Preserve tiles(0 To UBound(tiles) + 1) As String
            tiles(UBound(tiles)) = bq + CStr(i) + "Y"
        End If
        If i >= 38 And i <= 56 Then
            ReDim Preserve tiles(0 To UBound(tiles) + 1) As String
          tiles(UBound(tiles)) = bq + CStr(i) + "Z"
       End If
    If i >= 28 And i <= 55 Then
            ReDim Preserve tiles(0 To UBound(tiles) + 1) As String
          tiles(UBound(tiles)) = bq + CStr(i) + "AA"
       End If
    Next i
    GetTiles = tiles
    End Function

 

[cerfani]

put a breakpoint on the line ...

[COLOR=#333333]sheet.Cells(i, 1).EntireRow.Copy sheet3.Cells(K, 1)[/COLOR]

bool variable is false when you don't think it should be.

Apparently in that loop...

If tile = element

does not ever result in a True, maybe this test is bad?

 

[fahadun]

so how to fix it?