Re-numbering Excercise Using VBA

[Timbo]

I have a Base Data Workbook called Membership Book.

In Column A Starting in Row 8 there is a number unique to the data t
the right of it starting at one and going down to 964.

In Column J is an Location Identifier i.e. Blake's, Bradley, Coac
Road, Cooke's or Decoy.

In Column P is a status Resigned or otherwise.

I want a macro that will run down the status column P ignoring all o
the Resigned.

When it finds an entry which is anything other than Resigned or a blan
cell I want it to change the number in column A for that row to 1 an
remember what was originally in there say 14.

Then I want it to go to the Location Indicator in Column J. Thes
locations have a sheet of their own in the same format.

I then want the macro to go to the sheet for that row, select Column
search for 14 and replace with 1.

Then go back and continue the search down the Membership Book an
allocate 2 to the next number to be changed and so on.

Is this possible

 

[Per Jessen]

Hi

Try this (not tested):

Sub AAA()

counter = 1
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 8 To LastRow
If Not Cells(r, "P").Value = "Resigned" Then
ID = Cells(r, "A").Value
Cells(r, "A") = counter
Location = Range("J" & r).Value
Set f = Sheets(Location).Range("A:A").Find(what:=ID,
lookat:=xlWhole)
f.Value = counter
counter = counter + 1
End If
Next
End Sub

Regards,
Per