Search code examples
excelvbacopy-pastestring-matchingworksheet-function

Find first 30 string matches & paste at location A, then paste remaining string matches at location B


This code will search row E of one of my worksheets for the word 'white' and paste names on that row into my other worksheet. What i want it to do is paste the first 30 matches counting from the first match in x and the remainder of the matches at the second defined x. instead of searching from row 2 to 30 which is what it is doing now. if this doesn't make sense, the title is plain enough. Thanks!

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
Set Sh1 = ThisWorkbook.Worksheets("All Trades")
Set Sh2 = ThisWorkbook.Worksheets("As-Of Trades")
Sh1.Select

Sh2.Cells(1, 1).Value = "Account"
Sh2.Cells(1, 2).Value = "Amount"
lr = Sh1.Cells(Rows.Count, "A").End(xlUp).row
x = 2
For r = 2 To 30
    If Range("E" & r).Value = "WHITE" Then
        Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
        Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
        x = x + 1
    End If
Next r
x = 35
For r = 31 To lr
    If Range("E" & r).Value = "WHITE" Then
        Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
        Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
        x = x + 1
    End If
Next r
Sh2.Select
End Sub

Solution

  • Sub As_Of_Analysis_Sorting()
    Dim lr As Long, lr2 As Long, r As Long, x As Long
    Dim i as Long
    Set Sh1 = ThisWorkbook.Worksheets("All Trades")
    Set Sh2 = ThisWorkbook.Worksheets("As-Of Trades")
    
    
    Sh2.Cells(1, 1).Value = "Account"
    Sh2.Cells(1, 2).Value = "Amount"
    lr = Sh1.Cells(Rows.Count, "A").End(xlUp).Row
    
    x = 2
    i = 0
    For r = 2 To lr
        If Sh1.Range("E" & r).Value = "WHITE" Then
            Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
            Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
            x = x + 1
            i = i + 1
            If i = 30 Then x = 35
        End If
    Next r
    
    Sh2.Select
    End Sub