Re-numbering Excercise Using VBA
 

I have a Base Data Workbook called Membership Book.

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

In Column J is an Location Identifier i.e. Blake's, Bradley, Coach
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 of
the Resigned.

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

Then I want it to go to the Location Indicator in Column J. These
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 A
search for 14 and replace with 1.

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

Is this possible?


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79843

Re-numbering Excercise Using VBA
 

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

"Timbo" skrev i meddelelsen
...

I have a Base Data Workbook called Membership Book.

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

In Column J is an Location Identifier i.e. Blake's, Bradley, Coach
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 of
the Resigned.

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

Then I want it to go to the Location Indicator in Column J. These
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 A
search for 14 and replace with 1.

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

Is this possible?


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79843