I have a excel file with the following structure

Profile     Gene  Refseq
Panel1_yes   AAA   NA123
Panel1_yes   BBB   NA456
Pane_no      CCC   NA123
Panel1_yes   DDD   NA123

I want to iterate over Profile column and if value match a list of values, generate a new column that concatenate columns Gene and Refseq in a new book

Profile      New_column
Panel1_yes    AAA_NA123
Panel1_yes    BBB_NA456
Panel1_yes    DDD_NA123

Searching on tutorial I cant found how to ask excel to do something in the row that the value has matched with the element of my list.

Sub test()

Dim awb As Workbook
Dim ws As Worksheet

Dim a_lastrow As Integer     'last row of column A
Dim b_lastrow As Integer     'last row of column B

    Set awb = ThisWorkbook
    Set ws = awb.Worksheets("Sheet1")

    With ws

        a_lastrow = .Range("A100000").End(xlUp).Row
        b_lastrow = .Range("B100000").End(xlUp).Row

        For r = 1 To a_lastrow

            If .Range("A" & r).Value = "My_list" Then
                .Range("B" & r).Value = ...
             End If

        Next r

    End With

    MsgBox ("done")

End Sub

CodePudding user response：

Here is an example that sets up both your source and the destination workbooks and shows you how to transfer the data between them. Plus some helpful tips below:

Option Explicit

Sub testme()
    FindValues "Panel1_yes"
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("Panel1,Panel1_yes,Panel2_yes", ",")
    
    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

Here are the good habits for your VBA code:

1. Always use Option Explicit
2. Always be clear on what worksheet or range is being referenced
3. Use intermediate variables (such as lastRow) to help yourself make your code more readable. Yes, it's a few extra lines of code. But in many instances it can make your code faster (if that's a concern), but you'll find readability will always be a bigger help to you in the long run.

Good luck!