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!