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

Warpug

New Member
Joined
Apr 13, 2017
Messages
15
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Untested, but maybe this:

Code:
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

New Member
Joined
Apr 13, 2017
Messages
15
Untested, but maybe this:

Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top