My Code has a Tick it wont paste to the right row.

[Warpug]

For some reason my data keeps being pasted to row three on my target sheet. I wanted it to begin in row 2 on the target sheet, but this code is doing something i dont understand. The first hit I get that fits my criteria, is in row 3 on my Data sheet, and its being placed in row three on my target sheet leaving row two blank and then each hit after the first is going on the next row. In this small test data set I have hits on rows 3,5 and 7 but they are being populated on the Target sheet on rows 3,4 and 5. Please help.

Sub SIDMOVER2()
Dim dsht As Worksheet
Dim tsht As Worksheet
Set dsht = ThisWorkbook.Sheets("Data")
Set tsht = ThisWorkbook.Sheets("Target")
lRow = dsht.Cells(Rows.Count, 1).End(xlUp).Row
y = 2
For x = 2 To lRow
If dsht.Cells(x, 2) Like "[a-zA-Z]######" Then
tsht.Cells(y, 1) = dsht.Cells(x, 1)
tsht.Cells(y, 2) = dsht.Cells(x, 2)
tsht.Cells(y, 3) = dsht.Cells(x, 3)
ElseIf dsht.Cells(x, 2) Like "FUND" Then
tsht.Cells(y, 1) = dsht.Cells(x, 1)
tsht.Cells(y, 2) = dsht.Cells(x, 2)
tsht.Cells(y, 3) = dsht.Cells(x, 3)
Else
y = y + 1
End If
Next x
End Sub

 

[Akuini]

Untested, but maybe this:

y = 2
For x = 2 To LRow
If dsht.Cells(x, 2) Like "[a-zA-Z]######" Then
    tsht.Cells(y, 1) = dsht.Cells(x, 1)
    tsht.Cells(y, 2) = dsht.Cells(x, 2)
    tsht.Cells(y, 3) = dsht.Cells(x, 3)
    y = y + 1
ElseIf dsht.Cells(x, 2) Like "FUND" Then
    tsht.Cells(y, 1) = dsht.Cells(x, 1)
    tsht.Cells(y, 2) = dsht.Cells(x, 2)
    tsht.Cells(y, 3) = dsht.Cells(x, 3)
    y = y + 1
End If
Next x

 

[Warpug]

Untested, but maybe this:

y = 2
For x = 2 To LRow
If dsht.Cells(x, 2) Like "[a-zA-Z]######" Then
    tsht.Cells(y, 1) = dsht.Cells(x, 1)
    tsht.Cells(y, 2) = dsht.Cells(x, 2)
    tsht.Cells(y, 3) = dsht.Cells(x, 3)
    y = y + 1
ElseIf dsht.Cells(x, 2) Like "FUND" Then
    tsht.Cells(y, 1) = dsht.Cells(x, 1)
    tsht.Cells(y, 2) = dsht.Cells(x, 2)
    tsht.Cells(y, 3) = dsht.Cells(x, 3)
    y = y + 1
End If
Next x

That worked! Thanks again

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.