Add Row and Value Based on Row Above

Hi All:

Hoping to get some help with this issue that just came up. I get a report that I run my macro against, but the provider changed up the data by removing a row of data I need. I'd like to add that row back and do a calculation. Attaching a sample file.

What I’d like to happen:

- Look for the value “#Assigned Bldg Visited” in Column B, and insert a blank row on the next line. In the example, Row 7 has the first occurance of “#Assigned Bldg Visit,” so I’d like row 8 to become the blank row. This will run until the last instance of “#Assigned Bldg Visited” is found in Column B.

Note: I’m focusing on the value “#Assigned Bldg Visited” because the raw data may have some blanks or extra rows, that I didn’t include in this sample.

- In the newly inserted blank row, column B, insert and the value “% Assigned Bldg Visited.”

- Do a calculation for each month based on the values found on the Assigned tab for each person and the “# Assigned Bldg Visited” for the month. For the sample data I only went out to April. The monthly calculation would be something like this for c35 on the Visit sheet: =C34/Assigned!B2. However, I think it would somehow have to know the number of for the specific person referenced in Column A on the“Assigned” tab. I haven’t thought this through yet, but something roughly like this:

Take the name from the row with “# Assigned Bldg Visits” and match to the name on the “Assigned” tab and use the number in column b of the “Assigned” tab for the calculation “%Assigned Bldg Visited.”

If this doesn’t make sense, I’ll try to do it visually with screenshots. Thanks a ton in advance for any assistance. This makes my head spin!

Thanks
WHH3

P.S. On a side note, is it possible to run a formula in a macro, but have the out put to the cell be the value returned, rather than the formula. Just curious. Thx!

Thanks for the feedback Skywriter. On the Desired Output tab I did manually type in the values but it was simply: Number of Visits in January/number of Visits Assigned (on the Assigned tab). So John Doe is asked to visit his 5 Bldg's each month.* In January he made a total of 3 visits. 3/5 = 0.6 or 60% =c7/5 in this case. I think I'm doing that math correctly but been one of those days. Of course this bring to light another thing for me to think about...what if John Doe visits the same bldg twice in a month. Eh, one step at a time.

*I just noticed I messed up the counts on the Assigned tab, which is probably why it didn't make sense to you. It should be 5, 5,6,4 for anyone working on this for me.

Thanks for the answer on the formula pasting. I guess I never thought about pasting right over the forumla!

Oh good deal! Thanks for help and guidance! I should have some time this afternoon or tomorrow to tinker with this! I'll post how it goes!

First pass at it - works perfectly! Thanks for taking the time to help out! Marking as Solved!