Trying to write a Loop to perform calculations within blank rows between populated rows

Good morning/afternoon/evening all. I hope someone is up for (what I think is) a challenge as I've reached the end of my meager expertise!

I'm using Excel 2010 with the workbook in question saved as an Excel 97-2003 Workbook in Compatibilty Mode.

I've been able to piece together a macro that searches Sheet1 (please see the attached workbook) for specific elements in column B then transfers that individual row to Sheet2. I've then sorted both Sheets 1 & 2 by column A, added labels and placed Sheet2's cursor in cell C5 (as per the attached workbook). Looking at Sheet 2 row 5 in the example, I now need to write a Loop statement that will sum columns C, F & G for each unique Rep Name in column A placing those resulting values in C5, F5 & G5, calculate D5 as (F5 * C5) and calculate E5 as (G5 * C5). I then need to copy the name "Emp1" into cell A5, add a label ("Combined Average" or something TBD) to cell B5, move down to the next blank row beneath the next unique Rep Name & repeat the process. Once all calculations are complete, I need to copy each resulting row back into Sheet1 beneath the corresponding unique Rep Name.

The information in and length of Sheet1 will vary from day to day, so the elements being transferred to Sheet2 will never be identical (the Supervisors will be pasting new information into Sheet1 cell A1 every day). Due to that I'm not able to complete Sheet2 with static cell references. The number of blank rows between unique Rep Names as you see in the example can be altered if necessary; I thought having a blank row above & beneath each unique Rep Name might make it easier to perform the calculations.

I don't believe the macro as it exists needs to be added here as it performs flawlessly (so far!). My sincere hope is that someone can assist with the loop statement as I've outlined. If you would like to see the code as it exists now, I'll be happy to add it at your request.

I look forward to any assistance! Thank you for reading my post.

let us take a look at the macro, your needs can be in bedded in it

See if this works for the first part of your rquirement

Please Login or Register  to view this content.

Another:

Please Login or Register  to view this content.

I was working with Sheet2 in your example?

Not sure about the changes you made for Columns F & G so you'll have to reedit this for those changes.

Please Login or Register  to view this content.

Thank you for your prompt reply, John! Unfortunately, I find that when I add that additional code it throws run-time error '91': Object variable or With block variable not set. The error is on the line "ws.Activate". Here's the entire code with the edits I had added. One other edit I hadn't mentioned earlier. The values in columns D & E in your loop beneath the line "NoData:" were supposed to have been the result in column F divided by the result in column C rather than the product of those two columns, so I also had to edit your code in the final loop for both lines that start "rcell.Offset(, N).Value..." Here's my code:

Please Login or Register  to view this content.

I hope I'm being clear for you. Thank you again for your attention!

I modified your code Try:

Please Login or Register  to view this content.

That works like a charm!! Thank you SO very much John, I appreciate your help and expertise! I'll be marking this "Solved" in just a moment!

You're welcome. Glad to help out and thanks for the feedback.

Good afternoon/evening/morning, all. After John Davis' invaluable help last week with my macro, I find one additional situation has cropped up: In the final steps of the macro, each unique employee's newly calculated row is transferred from sheet2 to the blank row beneath that unique employee's name on sheet1. The situation I'm seeing is that the first unique employee's name at the top of sheet2 is added to the bottom of sheet1 to row 500. Here's the code in question:

Please Login or Register  to view this content.

Is it possible to change the range where I've commented "Here's where the calculated row is transferred from Sheet2 to Sheet1" so that it only searches to the last occupied cell in columnA & doesn't go farther?

I'm sorry if I haven't explained this sufficiently. Hope y'all can understand this.

I appreciate any response.

Maybe:

Please Login or Register  to view this content.

That works absolutely PERFECTLY! Once again, my thanks to you, John! You've proven yourself invaluable again! I was confident that's what needed to be done, but I just didn't know how to write the code for that line. My sincere gratitude to you for all your efforts!

Again welcome. And Glad to hear it works for you.

Good Morning/Afternoon/Evening, all. Unfortunately for me, I'm back with another brows furrowed, scratching my head question. It seems that the code that John H Davis so kindly helped me with is displaying a hiccup that I didn't notice until after the spreadsheet had been in use for a few days. In the final steps of John's solution for me, the row that was calculated on Sheet2 for each unique rep name is copied and placed back on Sheet1 into the blank row that was created beneath that unique rep's name. What one of the Supervisors here discovered is that the very last unique rep's calculated row isn't copied back to Sheet1; everyone's is except for that very last rep. The following is the code block where the calculation occurs on Sheet2 for each unique rep, labels are added and the row is copied back to Sheet1:

Please Login or Register  to view this content.

As in the past on this thread, I hope I've sufficiently explained the situation. If not, please share any questions you might have. If anyone would care to help, I'd sincerely appreciate it. Make it a great day!

This was set for a range to 500. I'm not certain that I understand your problem but try making this change.

Please Login or Register  to view this content.