Excel vba: repopulate numbers

Question

Thank you for taking the time to read my question. I'm working with a dynamic task scheduling system for my company. I have a list of tasks for about 10 artists which, the tasks and artists, all have unique ID numbers. I made a macro to grab all of a chosen artist's tasks, paste them into a separate sheet and then sort the tasks by their priority number which resides in say column G. (i.e.: 1st priority = 1.01, 2nd=1.02 and so forth) The trouble I run into is when a task is completed, canceled or when I want to change the priorities, There may be a skipped number in the priority list. For instance, if an Artist has completed task 2 (priority 1.02) then when I populate the list and sort by Priority, there will be 1st priority in row 1(Priority 1.01) and 2nd priority in row 2 (as Priority 1.03 instead of 1.02) because the original priority 2 has been removed from the list.

I'd like to repopulate the task priority according to the row it resides in. For instance, I¡¯d like to program the macro to say a task priority, or each cell in row G:G should be equal to 1+((activecell.row)/100) but exclude empty rows where tasks do not yet exist. (row 1 = 1+ (1/100) or 1.01, row 20 = 1+ (20/100) or 1.20)

Thank you kindly for your help and valuable time. If you need more information let me know and I'm not looking for you to write a bunch of code for me, just ideas of how I can accomplish this.

Answer

Brandon,

activecell.row

gives you the row number

so ID = Format((activecell.row/100)+1,"0.00")

Demo'd from the immediate window in the Visual Basic Editor in Excel:

ID = Format((activecell.row/100)+1,"0.00")

? activecell.Row

13 

? ID 1.13

I understood your example, but if the rows already have an ID, then not sure what the significance of

1.01 Row 1 1.03 row 2

so if you wanted to repopulate the the ID in column G

Sub Repopulate() Dim r as Range, cell as Range set r= range("G1",cells(rows.count,"G")) for each cell in r

 ' use the count function to see if the row is not empty (test columns A:J of that row)
 if application.CountA(r.offset(0,-6).Resize(1,10) > 0 then
    cell.Value = format((cell.row/100)+1,"0.00")
 end if

Next End Sub

Is that what you are looking for?

If not, then I guess I don't understand what you want.

perhaps a followup and give me an example of what you want to know