VBA Find and replace loop help needed

I need to add a section within an existing macro that takes a cell's address and looks for that address (as a string?) from the values within a range of cells - then offsets one column over to use that new value to replace its original value. I cannot hard code the cells in my mCell range, but also cannot figure out a functional loop that successfully moves through my mCell range and finds/replaces using values from another part of the worksheet. I'm new at VBA and keep getting errors and wind up defining a dozen ranges and strings trying to carry over the data. Any help would be greatly appreciated!

My code is looking for unmerged cells, and when it finds an unmerged cell, it needs to grab the correct value to put in there. Not all cells in my range mCell are unmerged, so this is a find/replace within a loop.

For example:
if unmerged mCell.address = "B20", then the macro finds the value "B20" in a designated range (in example below, was found in cell Q20), then offset one column over (to cell R20), then uses value of that cell (which is 6) to replace the value of mcell, such that the cell value of B20 (i.e., the active mCell) = 6. Then on to the next unmerged mCell...
row Column Q Col. R
18 B18(text) 5
19 B19 4
20 B20 6
21 B21 3


My existing code is: (simplified spreadsheet also attached that illustrates how the find/replace works)

Please Login or Register  to view this content.

Any suggestions on the macro loop?

Thank you - yes the end was messed up as well.
The top part of the code looks at my report, and if it sees the word "Table, it keeps the cells of the report unmerged, otherwise it merges the cells for formatting (the report gets emailed, so the merged cells keep everything in allignment around the table). The top part works great, but I need to fill in data into the table, if the table callout is present. This is a report template for various clients, so the layout and position (and presence or not) of a data table is variable. Otherwise I would just hard code the cell formula and be done. The real spreadsheet pulls data across 30-some sheets and compiles into a report. The attachment is quite simple in comparison. I just can't figure out how to carry the cell address as a searchable item to fill in the data I need, replace the original cell value and then move on to the next table cell. Thanks!