Selecting specific column in a macro excel sheet

Question

I have been using the code (code show below) suggested by PeterT in this public question

This macro works well if the data are in the first, second and third column like this of the sheet like this:

But now I have a more large spreadsheet and I would like to do the same but having one column and the other not immediately adjacent like this.

What modification do I need to do in the code to be able to do this.

Option Explicit

Sub testme()
    FindValues "Profile"
End Sub

Sub FindValues(ByVal value As String)
    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Set srcWB = ThisWorkbook
    Set srcWS = srcWB.Sheets("Sheet1")
    
    Dim dstWB As Workbook
    Dim dstWS As Worksheet
    Set dstWB = ThisWorkbook   '--- change to the new workbook
    Set dstWS = dstWB.Sheets("Sheet2")
    
    '--- find the end of the data in the destination sheet
    Dim dstRow As Long
    With dstWS
        dstRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
    End With
    
    With srcWS
        Dim lastRow As Long
        lastRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
        
        Dim i As Long
        For i = 1 To lastRow
            If IsInMyList(.Cells(i, 1).value) Then
                dstRow = dstRow + 1
                dstWS.Cells(dstRow, 1).value = .Cells(i, 1).value
                dstWS.Cells(dstRow, 2).value = .Cells(i, 2).value & "_" & .Cells(i, 3).value
            End If
        Next i
    End With
End Sub

Function IsInMyList(ByVal value As String) As Boolean
    Dim theList() As String
    theList = Split("Albinism and nystagmus 31-gene panel,TAAD 27-gene panel (R125),PCD 29-gene panel", ",")
    
    Dim item As Variant
    For Each item In theList
        If item = value Then
            IsInMyList = True
            Exit Function
        End If
    Next item
    IsInMyList = False
End Function

I have tried by changing the selection of the column where I think the code does that but I dont get non a error neither the desired results

For i = 1 To lastRow
            If IsInMyList(.Cells(i, 2).value) Then
                dstRow = dstRow + 1
                dstWS.Cells(dstRow, 2).value = .Cells(i, 2).value
                dstWS.Cells(dstRow, 4).value = .Cells(i, 4).value & "_" & .Cells(i, 7).value

Answer 1

Non-Adjacent Columns

• This is easily achieved by adding a string (srcColsList) containing the columns, writing the columns to an array (srcCols), and using the elements of the array as column 'identifiers', e.g. srcCols(0).
• You are not using the parameter Profile passed to your sub, so I removed it.
• Modify the A,F,D to fit your needs.

Option Explicit

Sub testme()
    FindValues
End Sub

Sub FindValues()
    
    Const srcColsList As String = "A,F,D" ' no spaces!
    Dim srcCols() As String: srcCols = Split(srcColsList, ",")
    
    Dim srcWB As Workbook: Set srcWB = ThisWorkbook
    Dim srcWS As Worksheet: Set srcWS = srcWB.Sheets("Sheet1")
    
    Dim dstWB As Workbook: Set dstWB = ThisWorkbook
    Dim dstWS As Worksheet: Set dstWS = dstWB.Sheets("Sheet2")
    
    '--- find the end of the data in the destination sheet
    Dim dstRow As Long
    With dstWS
        dstRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
    End With
    
    With srcWS
        Dim lastRow As Long
        lastRow = .Cells(.Cells.Rows.Count, srcCols(0)).End(xlUp).Row
        
        Dim i As Long
        For i = 1 To lastRow
            If IsInMyList(.Cells(i, srcCols(0)).Value) Then
                dstRow = dstRow + 1
                dstWS.Cells(dstRow, 1).Value = .Cells(i, srcCols(0)).Value
                dstWS.Cells(dstRow, 2).Value = .Cells(i, srcCols(1)).Value _
                    & "_" & .Cells(i, srcCols(2)).Value
            End If
        Next i
    End With

End Sub

Function IsInMyList(ByVal SearchString As String) As Boolean
    Const StringList As String _
        = "Albinism and nystagmus 31-gene panel," _
        & "TAAD 27-gene panel (R125)," _
        & "PCD 29-gene panel" ' no spaces!
    IsInMyList = IsNumeric(Application _
        .Match(SearchString, Split(StringList, ","), 0))
End Function