# For Next, If Then?

#### hviking

##### Board Regular
Got an assignment that I'm having a hard time with.

I have a spreadsheet full of date, in random lenghts etc... The only thing stable on the sheet, is all the data I need has LOCATION03AB in column b on the lines I need.

I.E:

Ticket #, Location, Priority, Date/Time, User
001, LOCATION01CA, 3, 12:00pm, user1
016, LOCATION03AB, 3, 11:03pm, user9
019, LOCATION01CA, 3, 11:08pm, user11
103, LOCATION01CA, 4, 10:03am, user9
110, LOCATION03AB, 2, 07:00am, user3

etc..

what I need is a way to look through the spreadsheet. If it finds LOCATION03AB, it copies the line to a new line on a seperate tab, then goes to the next one. Once it finds another one, it copies it to the next line on the seperate tab. etc....

So on Sheet2, it would only display:

Ticket #, Location, Priority, Date/Time, User
016, LOCATION03AB, 3, 11:03pm, user9
110, LOCATION03AB, 2, 07:00am, user3

Basic layout:

DO until end of page
If b2="LOCATION03AB" then copy and paste, go to next line.
Next.

Hope the description is understandable.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try

Code:
``````Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("B" & i).Value = "LOCATION03AB" Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub``````

Works exactly as I need it to.

Appricate it.

That part worked exactly as I was expecting, but it gave unexpected results.

It pulled all the ones with the correct location, but now I need to filter out users that are not to be included.

Would there be a way to look up the location, and if the user part is in a list on say sheet3, then copy the line, if not just skip it?

Or, to make it shorter, possible, instead of looking for the location, look for the list of users, and if a match copy it.

Which column is the user in in Sheet1 and Sheet3?

The list of users are on Sheet3 a1:a15 and they are in column E on Sheet1

Try

Code:
``````Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("B" & i).Value = "LOCATION03AB" Then
If IsNumeric(Application.Match(Range("E" & i).Value, Sheets("Sheet3").Columns("A"), 0)) Then
Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
Next i
End Sub``````

Works perfectly. Appreciate the help.

Replies
0
Views
241
Replies
6
Views
216
Replies
5
Views
311
Replies
1
Views
220
Replies
6
Views
1K

1,203,625
Messages
6,056,387
Members
444,862
Latest member
more_resource23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back