Excel Help

[Black Dynamite]

Hi guys,

Could do with a little bit of help on a project i'm working on.

I have a spreadsheet (lets call it spreadsheet 1) that lists client details and one field is vehicle reg number. This spreadsheet is from the previous month.

I have another spreadsheet that contains the same information but it is selected using different search criteria (spreadsheet 2).

What I need to do is write a macro to remove any records that appear in both spreadsheet 1 + 2 from spreadsheet 1.

I have this working but I would like to streamline it. Currently it copies a list of reg numbers from spreadsheet 2 into spreadsheet 1 and then runs the following code:

For each c In Range ("m2:m500")

If c.value = Range ("p3") Then
c.value = 123
End If

Next

For each c In Range ("m2:m500")

If c.value = Range ("p4") Then
c.value = 123
End If

Next

I then delete all rows where the value in P = 123.

This works, and is fine but the problem is I would have to do this for every cell in the "P" column which is a pain!

Is there a way I can "streamline" my code?

More than happy to go off and find the answer I just need somebody to point me in the right direction

Thanks for any help!

Edit: The data I am copying in can be in any order, so if a reg number in cell P4 = DJ55 6HH, this record could be in cell M370.

 

[mattx2]

you mean you want to increase p each time ?

something like this should work

dim i as integer

i=3

For each c In Range ("m2:m500")

If c.value = Range ("p"&i) Then
c.value = 123
i+1
End If

Next

 

[Black Dynamite]

Edit: Ignore me, i've managed to sort it by doing it another way!

Thanks for your help mate, you put me on the right path, really grateful!

Code if anybody is interested:

Dim i As Integer, c As Integer

i = c
c = 2

Do Until c = 500

If Range("M" & c) = Range("P" & i) Then

Range("M" & c) = 123
i = i + 1
c = 2

Else

c = c + 1

End If

Loop