I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

Rob -

One quick way around this is to give you collection of cells a name, say
FormulaRange. Then use code like this instead of wht you've got

Dim rng as Range
Application.Screenupdating = false
For Each rng in Range("FormulaRange")
rng.select
rng.formula = "=ITNBudgetFormula"
rng.Copy
rng.PasteSpecial xlvalues
Next rng
Application.Screenupdating = true

Duke

"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

try this idea. Modify to suittestremove comment on .formula=.value line

Sub formulaset()
Set frng = Range("h2:h4,h7:h9")
With frng
.Formula = "=h1+d8"
' .Formula = .Value
End With
End Sub

--
Don Guillett
SalesAid Software

"Rob" <NA wrote in message ...
I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better
way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

Rob -

While my earlier post contained code for selecting each cell in the group,
VBA code works much faster if you do not select cells. And the fact is that
rarely do you need to select a cell to accomplish your goal.

In this case, you may simply need to create your formula in VBA, using R1C1
references, assign the formula to the range of cells, then convert each cell
to a value like so, which doesn't select ANY cells and work very, very fast

With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With



"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

and my method should be even quicker

--
Don Guillett
SalesAid Software

"Duke Carey" wrote in message
...
Rob -

While my earlier post contained code for selecting each cell in the group,
VBA code works much faster if you do not select cells. And the fact is
that
rarely do you need to select a cell to accomplish your goal.

In this case, you may simply need to create your formula in VBA, using
R1C1
references, assign the formula to the range of cells, then convert each
cell
to a value like so, which doesn't select ANY cells and work very, very
fast

With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With



"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better
way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()

Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

True

"Don Guillett" wrote:

and my method should be even quicker

--
Don Guillett
SalesAid Software

"Duke Carey" wrote in message
...
Rob -

While my earlier post contained code for selecting each cell in the group,
VBA code works much faster if you do not select cells. And the fact is
that
rarely do you need to select a cell to accomplish your goal.

In this case, you may simply need to create your formula in VBA, using
R1C1
references, assign the formula to the range of cells, then convert each
cell
to a value like so, which doesn't select ANY cells and work very, very
fast

With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With



"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a better
way.
The problem seems to be that I can't use the copy function in a multiple
range. I guess I can't paste xlValues to multiple ranges either, so I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()

Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

Thanks Don and Duke. And the winner is......

I really appreciate your input and will trial both to see what works best in
my situation. Thanks for spending time to provide the best solution!

Rob

"Duke Carey" wrote in message
...
True

"Don Guillett" wrote:

and my method should be even quicker

--
Don Guillett
SalesAid Software

"Duke Carey" wrote in message
...
Rob -

While my earlier post contained code for selecting each cell in the
group,
VBA code works much faster if you do not select cells. And the fact is
that
rarely do you need to select a cell to accomplish your goal.

In this case, you may simply need to create your formula in VBA, using
R1C1
references, assign the formula to the range of cells, then convert each
cell
to a value like so, which doesn't select ANY cells and work very, very
fast

With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With



"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a
better
way.
The problem seems to be that I can't use the copy function in a
multiple
range. I guess I can't paste xlValues to multiple ranges either, so
I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()

Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub

Back again!

I thought it was working OK but the formula I used from Don is doing some
strange things I can't resolve.
The modified formula I'm using is:

Sub SetFormula()
Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134")
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") =
"=ITNBudgetFormula"
With frng
.Formula = .Value
End With
End Sub

However, although most of it works throughout the ranges, there is one
section that, as soon as the .Formula = .Value part executes, returns a
#N/A error (within the range G63:R110, namely G101:R110). Furthermore, some
of the data in other blocks of ranges show blank whereas all the others show
0 when all the data should be 0 because there is none yet. In fact,
immediately prior to executing the .Formula = .Value, all the cells show 0.
But as soon as .Formula = .Value executes these strange things happen.

Any ideas?

Rob






"Rob" <NA wrote in message ...
Thanks Don and Duke. And the winner is......

I really appreciate your input and will trial both to see what works best
in my situation. Thanks for spending time to provide the best solution!

Rob

"Duke Carey" wrote in message
...
True

"Don Guillett" wrote:

and my method should be even quicker

--
Don Guillett
SalesAid Software

"Duke Carey" wrote in message
...
Rob -

While my earlier post contained code for selecting each cell in the
group,
VBA code works much faster if you do not select cells. And the fact
is
that
rarely do you need to select a cell to accomplish your goal.

In this case, you may simply need to create your formula in VBA, using
R1C1
references, assign the formula to the range of cells, then convert
each
cell
to a value like so, which doesn't select ANY cells and work very, very
fast

With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With



"Rob" wrote:

I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value

What I have so far is shown below, but I'm sure there must be a
better
way.
The problem seems to be that I can't use the copy function in a
multiple
range. I guess I can't paste xlValues to multiple ranges either, so
I'm
being forced to handle each range, one at a time, which seems rather
cumbersome.

Sub Macro1()

Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub