.Value "issue"
Hello!
I've some code that adds formulas to a range of non-contiguous cells and then values out the cells to remove the formulas (for performance reasons). It's relatively simple code and the first part (adding the formulas) works great; it's the value out part that is causing problems:
Sub Run37Hours()
With Sheet2
..Unprotect
'feed in the values
.Range("AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49, AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78").Formula = _
.Range("AG5").Formula
Application.Calculate
' and value them out
.Range("AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49, AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78").Value = _
.Range("AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49, AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78").Value
..Protect
End With
End Sub
When the range is valued out, the value of the first cell becomes the value in EVERY cell (in the listed range). For example:
** after formulas are added
AG19 = 10
AG21 = 12
AG22 = 15
...
AG27 = 11
** after the formulas are removed
AG19 = 10
AG21 = 10
AG22 = 10
...
AG27 = 10
Is there a way to change my code to keep the proper values? or do I need copy/paste-values each sub-range to keep the values?
Thanks!
Ray
|