Excel Macro pattern matching filter

Question

Can anyone help me write this as a VB/excel macro.

I would like to select the following from one data sheet and write to another data sheet.

Select any rows where in column T the field contains the string SYDNEY-NEWC or contains the string F3 but does not contain F3 in the last 10 characters or where in column J the field contains the string SYDNEY-NEWC or contains the string M4 MTWY or contains the string F3 but does not contain the string F3 in the last 10 characters.

Answer 1

This is a start. It's rough and ready, missing a lot of error checking and the LEN function will fall over if the cell contains less than 10 characters. But this will move across rows that match your pattern:

Sub MigrateData2()

Dim intRowCount As Integer
Dim intTargetRowCount As Integer
Dim intColumnCount As Integer

Dim CheckString1 As String
Dim CheckString2 As String

intTargetRowCount = 1

    For intRowCount = 1 To Range("A1").CurrentRegion.Rows.Count

        CheckString1 = Cells(intRowCount, 20) ' contents of column T
        CheckString2 = Cells(intRowCount, 10) ' contents of column J

        If CheckString1 = "SYDNEY-NEWC" Or InStr(CheckString1, "F3") _
          Or InStr(Len(CheckString1) - 10, CheckString1, "F3") > 0 _
          Or CheckString2 = "SYDNEY-NEWC" Or InStr(CheckString2, "M4 MTWY") _
          Or InStr(CheckString2, "F3") _
          Or InStr(Len(CheckString2) - 10, CheckString2, "F3") > 0 Then

            For intColumnCount = 2 To 20 ' change this last number to however many columns you have

              Sheets("Sheet2").Range("A1").Cells(intTargetRowCount, intColumnCount - 1).Value = _
              Sheets("Sheet1").Range("A1").Cells(intRowCount, intColumnCount).Value

            Next intColumnCount

            intTargetRowCount = intTargetRowCount + 1

        End If

    Next intRowCount

End Sub

ASSUMPTIONS:

• Your source data is on Sheet1 and starts in cell A1
• Your target sheet is Sheet2 and you want to start writing the data from cell A1