For Next, If Then?

hviking

Board Regular
Joined
Mar 31, 2002
Messages
61
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.
 
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top