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

Hi Ray,

Am Mon, 29 Jun 2015 10:15:03 -0700 (PDT) schrieb Ray:

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?

what values are in the cells between? Can't you sum over the whole
range?
What is your formula in AG5?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus -

They're sub-totals, so I don't want to value them out; users may need to re-update them and putting back the sub-total formulas would be a bit messy.

//ray

Hi Ray,

Am Mon, 29 Jun 2015 10:27:25 -0700 (PDT) schrieb Ray:

They're sub-totals, so I don't want to value them out; users may need to re-update them and putting back the sub-total formulas would be a bit messy.

I don't know what your formula in AG5 is and where your times are and in
which format.
If your times are in AF in format h:mm then try for AG:

Sub Run37Hours()
Dim rngC As Range, myRng As Range
Dim mySum As Double


With Sheets("Sheet2")
.Unprotect
'feed in the values
Set myRng = .Range("AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47,
AG49, AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78")
myRng.NumberFormat = "[h]:mm"
For Each rngC In myRng
mySum = mySum + rngC.Offset(, -1)
rngC = mySum
Next

.Protect
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I understand the coding you suggest, but it's not doing what I had in mind. Perhaps some further details would help ...

The coding above is for one week; there are 51 other modules with the same coding, with just changes to the columns referenced (eg. AK, AS, AW, etc.)

The formula in AG5 is a vlookup, which uses looks up the value in column A and the # of columns to count over referenced in the same column (so, for original coding, lookup-column value is in AG4).

With these details, the coding you suggested (while useful) doesn't solve my original problem. I can populate the formulas into the desired range and with the accurate result ... but the value-out portion of the code results in every cell's value becoming that of cell AG19 (and NOT the formula result). See my example ...

Thanks for your effort!
Ray

Hi Ray,

Am Mon, 29 Jun 2015 10:53:17 -0700 (PDT) schrieb Ray:

The formula in AG5 is a vlookup, which uses looks up the value in column A and the # of columns to count over referenced in the same column (so, for original coding, lookup-column value is in AG4).

try it this way:

Sub Run37Hours()
Dim rngC As Range, myRng As Range
Dim mySum As Double


With Sheets("Sheet2")
.Unprotect
'feed in the values
Set myRng = .Range("AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47,
AG49, AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78")
With myRng
.Formula = Range("AG5").Formula
For Each rngC In myRng
rngC.Value = rngC.Value
Next
End With
.Protect
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional