Copy & paste in multiple areas using VBA

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


0
Rob
4/7/2005 9:11:52 AM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
609 Views

Similar Articles

[PageSpeed] 28

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
> 
> 
> 
0
DukeCarey (494)
4/7/2005 12:23:04 PM
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
donaldb@281.com
"Rob" <NA> wrote in message news:u74SLJ1OFHA.2680@TK2MSFTNGP09.phx.gbl...
> 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
>
>


0
Don
4/7/2005 12:23:57 PM
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
> 
> 
> 
0
DukeCarey (494)
4/7/2005 12:55:02 PM
and my method should be even quicker

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
> 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
> >
> >
> >


0
Don
4/7/2005 12:58:52 PM
True

"Don Guillett" wrote:

> and my method should be even quicker
> 
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
> > 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
> > >
> > >
> > >
> 
> 
> 
0
DukeCarey (494)
4/7/2005 1:35:01 PM
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" <DukeCarey@discussions.microsoft.com> wrote in message 
news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
> True
>
> "Don Guillett" wrote:
>
>> and my method should be even quicker
>>
>> -- 
>> Don Guillett
>> SalesAid Software
>> donaldb@281.com
>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
>> > 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
>> > >
>> > >
>> > >
>>
>>
>> 


0
Rob
4/8/2005 2:08:44 AM
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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
> 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" <DukeCarey@discussions.microsoft.com> wrote in message 
> news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
>> True
>>
>> "Don Guillett" wrote:
>>
>>> and my method should be even quicker
>>>
>>> -- 
>>> Don Guillett
>>> SalesAid Software
>>> donaldb@281.com
>>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
>>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
>>> > 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
>>> > >
>>> > >
>>> > >
>>>
>>>
>>>
>
> 


0
Rob
4/8/2005 5:17:12 AM
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
donaldb@281.com
"Rob" <NA> wrote in message news:u6ml6o$OFHA.2680@TK2MSFTNGP09.phx.gbl...
> 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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
> > 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" <DukeCarey@discussions.microsoft.com> wrote in message
> > news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
> >> True
> >>
> >> "Don Guillett" wrote:
> >>
> >>> and my method should be even quicker
> >>>
> >>> -- 
> >>> Don Guillett
> >>> SalesAid Software
> >>> donaldb@281.com
> >>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> >>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
> >>> > 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
> >>> > >
> >>> > >
> >>> > >
> >>>
> >>>
> >>>
> >
> >
>
>


0
Don
4/8/2005 12:09:34 PM
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" <donaldb@281.com> wrote in message 
news:%23mQ9TPDPFHA.1396@TK2MSFTNGP10.phx.gbl...
> 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
> donaldb@281.com
> "Rob" <NA> wrote in message news:u6ml6o$OFHA.2680@TK2MSFTNGP09.phx.gbl...
>> 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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
>> > 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" <DukeCarey@discussions.microsoft.com> wrote in message
>> > news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
>> >> True
>> >>
>> >> "Don Guillett" wrote:
>> >>
>> >>> and my method should be even quicker
>> >>>
>> >>> -- 
>> >>> Don Guillett
>> >>> SalesAid Software
>> >>> donaldb@281.com
>> >>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
>> >>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
>> >>> > 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
>> >>> > >
>> >>> > >
>> >>> > >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>
> 


0
Rob
4/9/2005 12:22:04 AM
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" <donaldb@281.com> wrote in message 
news:%23mQ9TPDPFHA.1396@TK2MSFTNGP10.phx.gbl...
> 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
> donaldb@281.com
> "Rob" <NA> wrote in message news:u6ml6o$OFHA.2680@TK2MSFTNGP09.phx.gbl...
>> 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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
>> > 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" <DukeCarey@discussions.microsoft.com> wrote in message
>> > news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
>> >> True
>> >>
>> >> "Don Guillett" wrote:
>> >>
>> >>> and my method should be even quicker
>> >>>
>> >>> -- 
>> >>> Don Guillett
>> >>> SalesAid Software
>> >>> donaldb@281.com
>> >>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
>> >>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
>> >>> > 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
>> >>> > >
>> >>> > >
>> >>> > >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>
> 


0
Rob
4/9/2005 5:16:46 AM
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
donaldb@281.com
"Rob" <NA> wrote in message news:ePx%23UNMPFHA.3408@TK2MSFTNGP14.phx.gbl...
> 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" <donaldb@281.com> wrote in message
> news:%23mQ9TPDPFHA.1396@TK2MSFTNGP10.phx.gbl...
> > 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
> > donaldb@281.com
> > "Rob" <NA> wrote in message
news:u6ml6o$OFHA.2680@TK2MSFTNGP09.phx.gbl...
> >> 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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
> >> > 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" <DukeCarey@discussions.microsoft.com> wrote in message
> >> > news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
> >> >> True
> >> >>
> >> >> "Don Guillett" wrote:
> >> >>
> >> >>> and my method should be even quicker
> >> >>>
> >> >>> -- 
> >> >>> Don Guillett
> >> >>> SalesAid Software
> >> >>> donaldb@281.com
> >> >>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> >> >>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
> >> >>> > 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
> >> >>> > >
> >> >>> > >
> >> >>> > >
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >
> >
>
>


0
Don
4/9/2005 12:29:22 PM
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
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:e7G8H$PPFHA.3880@tk2msftngp13.phx.gbl...
> 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
> donaldb@281.com
> "Rob" <NA> wrote in message
news:ePx%23UNMPFHA.3408@TK2MSFTNGP14.phx.gbl...
> > 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" <donaldb@281.com> wrote in message
> > news:%23mQ9TPDPFHA.1396@TK2MSFTNGP10.phx.gbl...
> > > 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
> > > donaldb@281.com
> > > "Rob" <NA> wrote in message
> news:u6ml6o$OFHA.2680@TK2MSFTNGP09.phx.gbl...
> > >> 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:uFMxm$9OFHA.3076@TK2MSFTNGP12.phx.gbl...
> > >> > 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" <DukeCarey@discussions.microsoft.com> wrote in message
> > >> > news:075AA94D-9FC6-4F1A-9197-52FDD883D1DC@microsoft.com...
> > >> >> True
> > >> >>
> > >> >> "Don Guillett" wrote:
> > >> >>
> > >> >>> and my method should be even quicker
> > >> >>>
> > >> >>> -- 
> > >> >>> Don Guillett
> > >> >>> SalesAid Software
> > >> >>> donaldb@281.com
> > >> >>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in
message
> > >> >>> news:C7CF36DB-BF09-4633-BB6B-24B383FBC32F@microsoft.com...
> > >> >>> > 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
> > >> >>> > >
> > >> >>> > >
> > >> >>> > >
> > >> >>>
> > >> >>>
> > >> >>>
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>


0
Don
4/11/2005 1:09:51 PM
Reply:

Similar Artilces:

problems came up in the following areas during load
I get this message when i copy either text or a graphic from the internet (using IE)and attempt to paste it into MS Publisher. The error dialog box says "Problems During Load"...this box normally apprears 20-30 seconds after I click paste. Once I hit OK, the text or graphic will appear. It never did this before. Any suggestions? CPG Co wrote: > I get this message when i copy either text or a graphic from the internet > (using IE)and attempt to paste it into MS Publisher. The error dialog box > says "Problems During Load"...this box normally apprears 20-...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...

SOS! SharePoint Install
I downloaded SharePoint Services 2.0 and installed it on my Windows Server 2003 Std. server where CRM 3.0 is installed. Now, I can't use CRM. When I double click the icon I get a message that says "The Server is Unavailable". Major disaster! Does anyone have any ideas? Thanks in advance. Did you reboot the server? "Lloyd" wrote: > I downloaded SharePoint Services 2.0 and installed it on my Windows Server > 2003 Std. server where CRM 3.0 is installed. Now, I can't use CRM. When I > double click the icon I get a message that says "The Server is...

Run-time error 438
I'm trying to find out why this code is not working. With Worksheets("Multi-period Code data").ListBox1 .Clear For FillCount = 1 To Worksheets("ClassCodes").Range("E1") .AddItem Worksheets("ClassCodes").Range("C1").Offset(FillCount, 0) Next FillCount .ListIndex = Worksheets("Multi-period Code data").Range("O1").Value End With Just to clarify, there is a sheet named "Multi-period Code data" and ListBox1 exists on the sheet. The err...

Copy Cells with VBA
Hello, I'd like a VBA macro that would copy in row C4 of worksheet A the content of row E11 of worksheet B. Is that possible ? Regards, Hi Jef If both workbooks are open try this I think you mean a range instead of a row? Workbooks("A.xls").Sheets("sheet1").Range("C4").Value = _ Workbooks("B.xls").Sheets("sheet1").Range("E11").Value -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:637A2067-D2D9-4607-B2D2-B03884429075@microsoft.com... > He...

Deleting 1 folder across multiple Mailboxes
We created a duplicate junk mail folder and want to delete it across multiple mailboxes. Is there a utility that will do this or do we need to open each mailbox individually? We have 1200 mailboxes. Thank you Exmerge.exe will do this...it is available at download.microsoft.com. "Griff" <Griff@discussions.microsoft.com> wrote in message news:886F1600-5F60-43F1-9F64-968C2F0FB115@microsoft.com... > We created a duplicate junk mail folder and want to delete it across > multiple > mailboxes. Is there a utility that will do this or do we need to open > each &...

Automate copy command
I'm processing a large data file from which I want to extract blocks of data by means of a template, this is only the first of many data files. I've used the vlookup command to match start and end timestamps and to return an index number that cooresponds to the start and end row numbers. How can I automate the copying of the blocks of data into a new sheet (or file)? ...

Real Programmers (TM) use MSFT C# not Linux languages (sez an expert)
Don't take my word for it, see what this nationally syndicated author and computer programming guru says... and note the reader's comment at the very end...about Linux...I think he's talking about Linux. Anyway the takeaway executive summary of this article is simple: C# rulz. RL http://programmingzen.com/2010/06/23/how-microsoft-is-changing-the-programm= ing-world/ How Microsoft is changing the programming world Posted on Jun 23rd, 2010 in .NET, Programming Languages | 47 comments Several years ago I knew a programmer, we=92ll call him Joe, who fancied himsel...

Can you store clipboard text and pick it up by copy it again?
I have a catch 22 - impossible actions here: A/ I want to have the sheet protected so users can't ruin all cells in the sheet B/ I have to Unprotect the sheet in the sub event Worksheet_Activate to be able to do some format macros C/ I want users to allow to copy text from one sheet and paste in this one. D/ The clipboard wipes out by the Unprotect command E/ The user have nothing to paste in... Is there a solution? /Regards --------------------------------------------------------------- ------------------------- Earl Kiosterud wrote: --------------- Imbecill, CutCopyMode gets turned...

pasting 1 cell into merged cells
Is this possible at all? I get an error: cannot change part of a merged cell. thanks, dominique Hi this is one of the drawbacks of merged cells. Therefore try to avoid these kind of formating -- Regards Frank Kabel Frankfurt, Germany Dominique Schroeder wrote: > Is this possible at all? > > I get an error: cannot change part of a merged cell. > > thanks, > > dominique You can get around the error message by pasting into the function lin above the worksheet. Look for the fx just above your lettered columns, click just right of the fx in the white line and ...

Outlook 2003 error when trying to use WordMail
Hi, Outlook 2003 has recently started reporting the following error, when trying to use Word 2003 as the MAil Editor. I have tried all the uslal things including Uninstalling Office, clearing the main registry settings and re installing, but nothing seems to work, does anyone have any idea what is causing this and more importantly how to solve it: "Word is unavailable, not installed, or is not the same version as outlook. The outlook email word editor will be used instead. an OLE registration error occured. The program is not correctly installed run Setup program again" Gavin,...

Query--using Access 2007
I have a tblAddress table with a lookup field called TypeofAddressID. A company may have many addresses with different TypeofAddressID—such as 1 = Business, 2 = Mailstop, 3 = PO Box, and so on. I need to pull out TypeofAddressID # 1. Then if a 1 is not available, give me the type that is (which may be the PO address or Mailstop). I tried DLookup, Xor, and IIf([TypeofAddressID]=1, 1, IIf([TypeofAddressID]=2, 2, IIf([TypeofAddressID]=3, 3, 4))), but it is listing all addresses per company instead of either or. Any suggestions? -- Message posted via http://www.accessmonster....

Can I copy formulas "as they are" when they are not static
Hi, I have numerous cells with formulas in them, and I would like to transfer them to other location without formulas changing. They need to be dynamic before and after, but not during this transfer Don't ask me why ;-) -Sirritys Find and replace all "=" with "~", move and change back -- Kind regards, Niek Otten Microsoft MVP - Excel "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154937837.006411.154950@m73g2000cwd.googlegroups.com... | Hi, | | I have numerous cells with formulas in them, and I would like to | transfer them to...

popup calendar with multiple months
I currently have popup calender in Excel to fill in cells with the date. I would like to be able to view 3 months at a time How can I change my set up to allow this. ...

Pasting problems
I am using excel to link with another application (from which I am taking some data). The time to get this data can vary. If it finds the data in the time I have allowed for it, it will paste this perfectly into excel. If it does not find the data in time, I assume that it is trying to paste something which is not there. I get a runtime 1004 error Paste method of worksheet class failed. Is there anyway around this. Can I write a loop procedure to only try to paste once there is something in the clipboard. Any help would be much appreciated. Thanks Chip Pearson has some nice notes at:...

Setting excel to landscape and setting print area from access
I have an excel file that I create from access and I was wondering if there is an easy way to set the page setup to landscape and to set the print area width from access. Is there an easy way to do this? I created a macro with the excel macro recorder but I am not sure if I can somehow use this code to have access do this. Any direction on this would be appreciated. thanks [code] With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With Active...

Images print pixles in white area. Why?
I print literature with publisher. All the images are linked. I upgraded to Office 2003, but now when I print the literature, the white area of the image is covered with a bunch of pixles. Why? I've tried to: 1- reset the image. 2- change it to an embedded image. 3- re-link the image 4- added the same image again, but the same result. The pixels are portions of the image that was retained when you edited the picture. How do the pictures look in an editing program? Have you tried erasing the pixels in your editing application. What extension are the images? -- Mary Sauer MSFT M...

If formula that looks at multiple cells and values, and then calul
I'm trying to put together a production spreadsheet for mechanical manufacturing that figures out a length of one piece of material based on other dimensions. It's for storm shutter parts and the cut dimensions need to be calculated from the field measures. There are many types of tracks available and the blade length is dependant upon the finish height, track type and structure. I need the formula to look at 5 different cells that may or may not have data for the top track and only use the data from the one that's filled out. Same for the bottom track for a total of 10 ...

can only see one page of a multiple page publisher file
today can only see the first page of a file in publisher....checked the attributes removed the read only....could not remove from overall i.e. my documents...can pull up on another computer via network and see all....so file appears to be okay...can also print all pages, but can only see and work with page one...HELP.....AND A BIG THANKS TO WHOMEVER!!!! What happens when you try to click on the other pages? Do you have the status bar turned on? (View - Status bar) -- JoAnn Paules MVP Microsoft [Publisher] "CON" <CON@discussions.microsoft.com> wrote in message news:...

how can I enter 453. to show as 453 instead of 4.53 when using th.
I'm not sure how to use the decimal when entering numbers in excel. I want to use the fixed decimal space as to so when I just type in 453 it appears as 4.53 But when I want to enter 60 and I enter 60. I get .60 Any thoughts? Enter 60. with the decimal point at the end. In article <B343D637-7039-48CA-AB16-6B389D3A441A@microsoft.com>, "Scott" <Scott@discussions.microsoft.com> wrote: > I'm not sure how to use the decimal when entering numbers in excel. I want > to use the fixed decimal space as to so when I just type in 453 it appears as >...

I cant use englisch function names in a swedich version of excel
Hi, Why can't I use Englisch named function names, like =ROUND(...) in a sheet when I have a Swedich version of excel. Excel will tell me the error "#Name?" when I open an Englisch version worksheet or if I try to enter an englisch function name. But if I write a macro with the englisch function name inside the code and I call that macro in the sheet the function will be translated to the swedich function name. Thanks Pelle Hi this is just the way Excel works. For a function translation see: http://www.contextures.com/functions.html -- Regards Frank Kabel Frankfurt, Germany ...

Copy and paste two named ranges together.
I am attempting to copy and combine two named ranges of equal size into a blank spreadsheet. How does one copy the first named range and concurrently seperate each copied row with a blank row into the blank spreadsheet, and copy the second range and paste those copied records into the blank rows? I am looking for a systematic way of doing this consolidation. There can be hundreds of rows of data. Also the named ranges can very in size month to month. The end result is to combine two ranges for a journal entry upload into a financial accounting entry. The two arrays represent the ...

Copy Subtotals to another worksheet
When using excel sub total function. How do I copy the sub total to another worksheet? Hi Reduce the grouping to show just the information you want. Hit F5 and select Visible Cells. Ctrl+C to copy. Move to where you want the totals and click Edit / Paste Special / Values. -- Andy. "Webs" <Webs@discussions.microsoft.com> wrote in message news:50CA209E-6B45-487E-8382-D85AAAB64DAB@microsoft.com... > When using excel sub total function. How do I copy the sub total to > another > worksheet? ...

Difficult Countif with multiple sheets
Hi! Im using the following formula to get a countif =COUNTIF(INDEX('�1'!4:4;1;$AI$2):INDEX('�1'!4:4;1;$AI$3);0) which is filling down to the cell 1000 The AI2 cell is {=MIN(IF('K1'!B2:IV2=S3;COLUMN('�1'!B2:IV2)))} and AI3 cell is {=MAX(IF('K1'!B2:IV2=S3;COLUMN('K1'!B2:IV2)))} and the S3 cell is a list having '01,'02','03',04' etc. The values ar getting from the cells A1:I1 from the sheet 'K1' I have a sheet called 'K1' and it is like A B C D E F G H I 1 01 01 01 01 02 02 02 03 ...

How to Insert clips in Outlook using Clip Art Organizer
My Outlook 2003 "Insert" menu used to have the option of using the Clip Art Organizer program to search for clip art to insert into Outlook. This option has disappeared and I have not been able to find out how to get it back. Any ideas? "Tamara" <Tamara@discussions.microsoft.com> wrote in message news:CADF265E-7DBB-46EB-8077-CA78137FE4C3@microsoft.com... > My Outlook 2003 "Insert" menu used to have the option of using the Clip Art > Organizer program to search for clip art to insert into Outlook. This > option > has disappeare...