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.
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")
? 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