I've got a variety of worksheets that are quite large and have formulas that have been copied down and across. The formulas use relative and absolute references, so some of the cell references change; whereas, others do not.
As others work on my files (or I make an error), sometimes a formula gets overwritten in a strange location. For example, if there's a block of formulas in D50:Z2000, the user might try to press Ctrl+C to copy something, but miss the ctrl press and actually enter a C into the cell.
I'd like to have a macro that can be run to produce a check sheet that basically shows if formulas are consistently entered across columns and down rows. An example is at the bottom of this post.
Is there a way to use VBA to determine if the formulas are consistent? The only thing I can think of is to create a dummy worksheet and just enter the formula and, cell by cell, copy it over 1 cell and compare the result to the source. This would probably be really slow, especially compared to some sort of built in functionality or handling based on blocks of cells.
Here's an example of a possible solution that I saw somewhere in the past. If each character below is a cell and "F" represents a new, non-copied formula and ">" represents a copied across formulas and "v" represents a copied down formula:
FFFF>>>
vvvv>>>
vvvv>>>
vvFv>>>
vvFv>>>
vvvv>>>
The 2 bold F's show that there's a break in the copied down formulas, which would indicate a likely error.
Thanks in advance for any alternate suggestions or solution ideas!
Rob
As others work on my files (or I make an error), sometimes a formula gets overwritten in a strange location. For example, if there's a block of formulas in D50:Z2000, the user might try to press Ctrl+C to copy something, but miss the ctrl press and actually enter a C into the cell.
I'd like to have a macro that can be run to produce a check sheet that basically shows if formulas are consistently entered across columns and down rows. An example is at the bottom of this post.
Is there a way to use VBA to determine if the formulas are consistent? The only thing I can think of is to create a dummy worksheet and just enter the formula and, cell by cell, copy it over 1 cell and compare the result to the source. This would probably be really slow, especially compared to some sort of built in functionality or handling based on blocks of cells.
Here's an example of a possible solution that I saw somewhere in the past. If each character below is a cell and "F" represents a new, non-copied formula and ">" represents a copied across formulas and "v" represents a copied down formula:
FFFF>>>
vvvv>>>
vvvv>>>
vvFv>>>
vvFv>>>
vvvv>>>
The 2 bold F's show that there's a break in the copied down formulas, which would indicate a likely error.
Thanks in advance for any alternate suggestions or solution ideas!
Rob
