Dynamic variables

Hello, I'm new to the forum. Hope i can help!

Im writing a macro that takes a list of raw data and sorts it in two levels; first by project number then by person. The paramter being sorted is how many hours they worked on a project. These are my fields:

excell row number- Project Number - Person - Hours
1 - 12345 - Timothy Dalton - 4
2 - Rick Allen - 10
3 - Rowan Atkinson - 12
4
5


My raw data looks like this:

Project Number- Person - Hours- row number
12345 - Timothy Dalton - 4 - 1
12345 - Rick Allen - 10 - 2
12345 - Rowan Atkinson - 12 - 3


I've written a macro that sorts these records but I would like to automate the program further by removing the row number. What i want to do is have the program read the person's name in the raw data, then search the list and either enter in the number of hours in the correct place, or if the person's name is not found in the list, add it (i have a counter containing the number of entries per project and a "row starting" entry for each project)

i want to add a new entry via the insertion formula:

Please Login or Register  to view this content.

Now the problem here is that i need to dynamically indicate the row number when the program is run. If the persons name is not found then the program will insert a new line at the bottom of the entries and then enter in the new information. A variable will act as a pointer to the row numbers so i want the code to be:

Please Login or Register  to view this content.

Where K is my pointer, but this doesnt work, so how i can i dynamically indicate the row where i want to insert a line, or is there a better way to go about this?

Thanks Bud, that worked wonders. Im learning VB and access as im working on this project. Its frustrating when you dont know the syntax!

The next, and final, step is that once i insert a new line and enter the new information (number of hours) I need to update the summation formula to include the new entry. So that now the new number of hours is taken into account.

This is a bit more tricky because i dont know how to refer to a specific cell dynamically. I recorded a macro of what i wanted to do and got this:

Please Login or Register  to view this content.

The summation formula is always in the same column, but obviously in different rows for different projects

Ramzy,

I've wrapped your code for you this time. Please read forum rules below before you post again.

Does this help. It inserts the sum formula in the activecell for the two cells above

Please Login or Register  to view this content.

VBA Noob