Copy & paste in multiple areas using VBA

[Rob]

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

[Duke Carey]

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
>
>
>

[Don Guillett]

try this idea. Modify to suit>test>remove 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
[email protected]
"Rob" <NA> wrote in message news:[email protected]...
> 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
>
>

[Duke Carey]

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
>
>
>

[Don Guillett]

and my method should be even quicker

--
Don Guillett
SalesAid Software
[email protected]
"Duke Carey" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

[Duke Carey]

True

"Don Guillett" wrote:

> and my method should be even quicker
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Duke Carey" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
> > >
>
>
>

[Rob]

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" <[email protected]> wrote in message
news:[email protected]...
> True
>
> "Don Guillett" wrote:
>
>> and my method should be even quicker
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Duke Carey" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > >
>> > >
>> > >
>>
>>
>>

[Rob]

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 news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> True
>>
>> "Don Guillett" wrote:
>>
>>> and my method should be even quicker
>>>
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> [email protected]
>>> "Duke Carey" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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
>>> > >
>>> > >
>>> > >
>>>
>>>
>>>
>
>

[Don Guillett]

Since you did not mention what your formula is, I have NO idea what your
formula is. Test with what I sent and then modify.

Sub SetFormula()
Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
With frng
'No idea on this part
..formula = "ITNBudgetFormula"
perhaps ??????
'.formula=a1*b2 'or whatever

.Formula = .Value
End With
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Rob" <NA> wrote in message news:[email protected]...
> 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 news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> True
> >>
> >> "Don Guillett" wrote:
> >>
> >>> and my method should be even quicker
> >>>
> >>> --
> >>> Don Guillett
> >>> SalesAid Software
> >>> [email protected]
> >>> "Duke Carey" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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
> >>> > >
> >>> > >
> >>> > >
> >>>
> >>>
> >>>
> >
> >
>
>

[Rob]

Hi Don,

To run your code I've modified it as follows:
Sub SetFormula1()
Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
Sheet4.Unprotect
With frng
.Formula = "=ITNBudgetFormula"
.Formula = .Value
End With
Sheet4.Protect
End Sub

The code puts the formula in OK and it shows the correct data BUT as soon as
the line .Formula = .Value is executed all the data is returned to 0 except
for the range G102:R110 which becomes #N/A AND any data in range G94:R101
becomes 0.
I've tried 2 diff formulas to see if the formula could be the error but I
don't think it is as the formulas return the correct values, and the same
problem occurs with both. The formulas are:

1.
=IF($C63="",0,SUMPRODUCT(--(OldBudget!$C$8:$C$134=$C63),OldBudget!G$8:G$134))

2. =IF($C63="",0,VLOOKUP($C63,OldBudget!$C$8:$R$134,AD$1,FALSE))

(The code you supplied copies the formula nicely to the full ranges as
required, changing the cell references correctly and so, returning the
correct results The problem happens only when .Formula = .Value is
executed.)

What I'm doing (using VBA), is copying a complete worksheet called Budget to
another worksheet called OldBudget. I then enter or amend the data in
column C on the Budget worksheet for certain reasons. Then.....and this is
the process I'm having trouble with... I want the formula to find any data,
now in the OldBudget worksheet that might be still applicable, depending on
whether it matches the data in column C in the Budget worksheet.

Rob

"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> Since you did not mention what your formula is, I have NO idea what your
> formula is. Test with what I sent and then modify.
>
> Sub SetFormula()
> Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
> With frng
> 'No idea on this part
> .formula = "ITNBudgetFormula"
> perhaps ??????
> '.formula=a1*b2 'or whatever
>
> .Formula = .Value
> End With
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Rob" <NA> wrote in message news:[email protected]...
>> 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 news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> True
>> >>
>> >> "Don Guillett" wrote:
>> >>
>> >>> and my method should be even quicker
>> >>>
>> >>> --
>> >>> Don Guillett
>> >>> SalesAid Software
>> >>> [email protected]
>> >>> "Duke Carey" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>> > 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
>> >>> > >
>> >>> > >
>> >>> > >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>
>

[Rob]

Back again, Don.

I created a test workbook to eliminate any unforseen influences. In the
process of doing that I discovered that I'd made a small error to the ranges
which I fixed. Nevertheless, the problem still exists, except it's now a
bit clearer to me.
What's happening is, that when the .Formula=.Value executes, it pastes the
result of the data held in cells G8:R46 to all the other ranges, namely, to
G50:R59, and to G63:R110, and to G114:R122, and to G126:R134.
ie. the values shown in G50:R59 become the values from G8:R46
the values shown in G63:R110 also become the values from G8:R46 and so on.
It seems that because there are less cells in G8:R46 than in the range
G63:R110, that the remaining cells in that range show #N/A when the .Formula
= .Value executes.

The latest version of the code (that creates the error) to extract the data
from 1 sheet to the other is:

Sub SetFormula1()
'To extract OldBudget data to Budget's current account numbers
Sheet1.Activate
Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134")
frng.Select
Selection.ClearContents
With frng
.Formula = "=ITNBudgetFormula" 'See my other post for the actual
formula
.Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY!
End With
Range("C6").Select
End Sub

Prior to running the above code the following code is run to copy the data
from sheet 1 to sheet 2:

Sub TransferBudget()
'To copy budget data to OldBudget sheet
Sheet1.Range("C5:R975").Copy
Sheet2.Select
Range("C5").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Sheet1.Activate
End Sub

I hope you can see what the problem is because every variation I try has no
real effect and it's beyond my VBA knowledge to try something else.

Rob

"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> Since you did not mention what your formula is, I have NO idea what your
> formula is. Test with what I sent and then modify.
>
> Sub SetFormula()
> Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
> With frng
> 'No idea on this part
> .formula = "ITNBudgetFormula"
> perhaps ??????
> '.formula=a1*b2 'or whatever
>
> .Formula = .Value
> End With
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Rob" <NA> wrote in message news:[email protected]...
>> 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 news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> True
>> >>
>> >> "Don Guillett" wrote:
>> >>
>> >>> and my method should be even quicker
>> >>>
>> >>> --
>> >>> Don Guillett
>> >>> SalesAid Software
>> >>> [email protected]
>> >>> "Duke Carey" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>> > 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
>> >>> > >
>> >>> > >
>> >>> > >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>
>

[Don Guillett]

As I said before, I have no idea what your formula is and don't care to
"search" for it. Tested with this. If you need more assistance post your
formula or send me a SMALL wb.

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

--
Don Guillett
SalesAid Software
[email protected]
"Rob" <NA> wrote in message news:ePx%[email protected]...
> Back again, Don.
>
> I created a test workbook to eliminate any unforseen influences. In the
> process of doing that I discovered that I'd made a small error to the
ranges
> which I fixed. Nevertheless, the problem still exists, except it's now a
> bit clearer to me.
> What's happening is, that when the .Formula=.Value executes, it pastes the
> result of the data held in cells G8:R46 to all the other ranges, namely,
to
> G50:R59, and to G63:R110, and to G114:R122, and to G126:R134.
> ie. the values shown in G50:R59 become the values from G8:R46
> the values shown in G63:R110 also become the values from G8:R46 and so on.
> It seems that because there are less cells in G8:R46 than in the range
> G63:R110, that the remaining cells in that range show #N/A when the
..Formula
> = .Value executes.
>
> The latest version of the code (that creates the error) to extract the
data
> from 1 sheet to the other is:
>
> Sub SetFormula1()
> 'To extract OldBudget data to Budget's current account numbers
> Sheet1.Activate
> Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134")
> frng.Select
> Selection.ClearContents
> With frng
> .Formula = "=ITNBudgetFormula" 'See my other post for the
actual
> formula
> .Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY!
> End With
> Range("C6").Select
> End Sub
>
> Prior to running the above code the following code is run to copy the data
> from sheet 1 to sheet 2:
>
> Sub TransferBudget()
> 'To copy budget data to OldBudget sheet
> Sheet1.Range("C5:R975").Copy
> Sheet2.Select
> Range("C5").PasteSpecial Paste:=xlValues
> Application.CutCopyMode = False
> Sheet1.Activate
> End Sub
>
> I hope you can see what the problem is because every variation I try has
no
> real effect and it's beyond my VBA knowledge to try something else.
>
> Rob
>
> "Don Guillett" <[email protected]> wrote in message
> news:%[email protected]...
> > Since you did not mention what your formula is, I have NO idea what your
> > formula is. Test with what I sent and then modify.
> >
> > Sub SetFormula()
> > Set frng =
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
> > With frng
> > 'No idea on this part
> > .formula = "ITNBudgetFormula"
> > perhaps ??????
> > '.formula=a1*b2 'or whatever
> >
> > .Formula = .Value
> > End With
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Rob" <NA> wrote in message
news:[email protected]...
> >> 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
news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> True
> >> >>
> >> >> "Don Guillett" wrote:
> >> >>
> >> >>> and my method should be even quicker
> >> >>>
> >> >>> --
> >> >>> Don Guillett
> >> >>> SalesAid Software
> >> >>> [email protected]
> >> >>> "Duke Carey" <[email protected]> wrote in message
> >> >>> news:[email protected]...
> >> >>> > 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
> >> >>> > >
> >> >>> > >
> >> >>> > >
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Don Guillett]

You did not supply any data on sheet2 for testing but this should work from
sheet1 without selections.

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

Also, on sheet1 place cursor at cell g8>window>freeze panes>save


--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> As I said before, I have no idea what your formula is and don't care to
> "search" for it. Tested with this. If you need more assistance post your
> formula or send me a SMALL wb.
>
> Sub formulaset()
> Set frng = Range("h2:h4,h7:h9")
> With frng
> .Formula = "=h1+d8"
> ' .Formula = .Value
> End With
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Rob" <NA> wrote in message
news:ePx%[email protected]...
> > Back again, Don.
> >
> > I created a test workbook to eliminate any unforseen influences. In the
> > process of doing that I discovered that I'd made a small error to the
> ranges
> > which I fixed. Nevertheless, the problem still exists, except it's now
a
> > bit clearer to me.
> > What's happening is, that when the .Formula=.Value executes, it pastes
the
> > result of the data held in cells G8:R46 to all the other ranges, namely,
> to
> > G50:R59, and to G63:R110, and to G114:R122, and to G126:R134.
> > ie. the values shown in G50:R59 become the values from G8:R46
> > the values shown in G63:R110 also become the values from G8:R46 and so
on.
> > It seems that because there are less cells in G8:R46 than in the range
> > G63:R110, that the remaining cells in that range show #N/A when the
> .Formula
> > = .Value executes.
> >
> > The latest version of the code (that creates the error) to extract the
> data
> > from 1 sheet to the other is:
> >
> > Sub SetFormula1()
> > 'To extract OldBudget data to Budget's current account numbers
> > Sheet1.Activate
> > Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134")
> > frng.Select
> > Selection.ClearContents
> > With frng
> > .Formula = "=ITNBudgetFormula" 'See my other post for the
> actual
> > formula
> > .Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY!
> > End With
> > Range("C6").Select
> > End Sub
> >
> > Prior to running the above code the following code is run to copy the
data
> > from sheet 1 to sheet 2:
> >
> > Sub TransferBudget()
> > 'To copy budget data to OldBudget sheet
> > Sheet1.Range("C5:R975").Copy
> > Sheet2.Select
> > Range("C5").PasteSpecial Paste:=xlValues
> > Application.CutCopyMode = False
> > Sheet1.Activate
> > End Sub
> >
> > I hope you can see what the problem is because every variation I try has
> no
> > real effect and it's beyond my VBA knowledge to try something else.
> >
> > Rob
> >
> > "Don Guillett" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Since you did not mention what your formula is, I have NO idea what
your
> > > formula is. Test with what I sent and then modify.
> > >
> > > Sub SetFormula()
> > > Set frng =
> Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134")
> > > With frng
> > > 'No idea on this part
> > > .formula = "ITNBudgetFormula"
> > > perhaps ??????
> > > '.formula=a1*b2 'or whatever
> > >
> > > .Formula = .Value
> > > End With
> > > End Sub
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "Rob" <NA> wrote in message
> news:[email protected]...
> > >> 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
> news:[email protected]...
> > >> > 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" <[email protected]> wrote in message
> > >> > news:[email protected]...
> > >> >> True
> > >> >>
> > >> >> "Don Guillett" wrote:
> > >> >>
> > >> >>> and my method should be even quicker
> > >> >>>
> > >> >>> --
> > >> >>> Don Guillett
> > >> >>> SalesAid Software
> > >> >>> [email protected]
> > >> >>> "Duke Carey" <[email protected]> wrote in
message
> > >> >>> news:[email protected]...
> > >> >>> > 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
> > >> >>> > >
> > >> >>> > >
> > >> >>> > >
> > >> >>>
> > >> >>>
> > >> >>>
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>