[Ray]

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

[Claus Busch]

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

[Ray]

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

[Claus Busch]

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

[Ray]

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

[Claus Busch]

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

[Ray]

Hi Claus -

That worked perfectly ... and actually faster than my original code!

Thanks very much for your time & effort!

/ray

[GS[_6_]]

Why not...

With myRng
.Formula = Range("AG5").Formula
.Value = .Value
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Claus Busch]

Hi Garry,

Am Mon, 29 Jun 2015 20:28:44 -0400 schrieb GS:

Why not...

With myRng
.Formula = Range("AG5").Formula
.Value = .Value
End With

with this non-contiguous range you run in the same issue as the OP. All
cells then have the value of the first cell.


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

[GS[_6_]]

Hi Garry,

Am Mon, 29 Jun 2015 20:28:44 -0400 schrieb GS:

Why not...

With myRng
.Formula = Range("AG5").Formula
.Value = .Value
End With

with this non-contiguous range you run in the same issue as the OP.
All cells then have the value of the first cell.


Regards
Claus B.

Ah, yes! Of course! The group is Areas but Excel is treating it as from
the 1st address to the last address! In this case Union, perhaps?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Claus Busch]

Hi Garry,

Am Tue, 30 Jun 2015 02:03:31 -0400 schrieb GS:

Ah, yes! Of course! The group is Areas but Excel is treating it as from
the 1st address to the last address! In this case Union, perhaps?

I tested it with Union. But this also does not work.
I found no working way except the loop through the range.


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

[Claus Busch]

Hi again,

Am Tue, 30 Jun 2015 08:22:40 +0200 schrieb Claus Busch:

I tested it with Union. But this also does not work.
I found no working way except the loop through the range.

this will work:

Sub Run37Hours()
Dim myRng As Range
Dim i As Long

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 i = 1 To .Areas.Count
.Areas.Item(i).Value = .Areas.Item(i).Value
Next
End With
.Protect
End With

End Sub


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

[GS[_6_]]

This too, perhaps?


Sub Run37Hours()
Dim n&, sRefs$, vRefs

sRefs = "AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49,"
sRefs = sRefs & "AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78"
vRefs = Split(sRefs, ",")

With Sheets("Sheet2")
.Unprotect
'feed in the values
For n = LBound(vRefs) To UBound(vRefs)
With Range(vRefs(n))
.Formula = Range("AG5").Formula: .value = .value
End With
Next 'n
.Protect
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Claus Busch]

Hi Garry,

Am Tue, 30 Jun 2015 02:40:22 -0400 schrieb GS:

Sub Run37Hours()
Dim n&, sRefs$, vRefs

sRefs = "AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49,"
sRefs = sRefs & "AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78"
vRefs = Split(sRefs, ",")

With Sheets("Sheet2")
.Unprotect
'feed in the values
For n = LBound(vRefs) To UBound(vRefs)
With Range(vRefs(n))
.Formula = Range("AG5").Formula: .value = .value
End With
Next 'n
.Protect
End With
End Sub

into the area you now have the correct values. But the values of the
area is repeated in the other areas.


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

[GS[_6_]]

Hi Garry,

Am Tue, 30 Jun 2015 02:40:22 -0400 schrieb GS:

Sub Run37Hours()
Dim n&, sRefs$, vRefs

sRefs = "AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49,"
sRefs = sRefs & "AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78"
vRefs = Split(sRefs, ",")

With Sheets("Sheet2")
.Unprotect
'feed in the values
For n = LBound(vRefs) To UBound(vRefs)
With Range(vRefs(n))
.Formula = Range("AG5").Formula: .value = .value
End With
Next 'n
.Protect
End With
End Sub

into the area you now have the correct values. But the values of the
area is repeated in the other areas.


Regards
Claus B.

Well.., they all use the same formula. Perhaps the formula should be
loaded into a var...

Sub Run37Hours()
Dim n&, sFormula$, sRefs$, vRefs

sFormula = Range("AG5").Formula
sRefs = "AG19, AG21:AG27, AG29:AG35, AG39:AG44, AG47, AG49,"
sRefs = sRefs & "AG52, AG55:AG60, AG63:AG66, AG69, AG73:AG78"
vRefs = Split(sRefs, ",")

With Sheets("Sheet2")
.Unprotect
'feed in the values
For n = LBound(vRefs) To UBound(vRefs)
With Range(vRefs(n))
.Formula = sFormula: .value = .value
End With
Next 'n
.Protect
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion