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" wrote in message
...
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
"Rob" <NA wrote in message ...
Back again!
I thought it was working OK but the formula I used from Don is doing some
strange things I can't resolve.
The modified formula I'm using is:
Sub SetFormula()
Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134")
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") =
"=ITNBudgetFormula"
With frng
.Formula = .Value
End With
End Sub
However, although most of it works throughout the ranges, there is one
section that, as soon as the .Formula = .Value part executes, returns a
#N/A error (within the range G63:R110, namely G101:R110). Furthermore,
some
of the data in other blocks of ranges show blank whereas all the others
show
0 when all the data should be 0 because there is none yet. In fact,
immediately prior to executing the .Formula = .Value, all the cells show
0.
But as soon as .Formula = .Value executes these strange things happen.
Any ideas?
Rob
"Rob" <NA wrote in message ...
Thanks Don and Duke. And the winner is......
I really appreciate your input and will trial both to see what works
best
in my situation. Thanks for spending time to provide the best
solution!
Rob
"Duke Carey" wrote in message
...
True
"Don Guillett" wrote:
and my method should be even quicker
--
Don Guillett
SalesAid Software
"Duke Carey" wrote in message
...
Rob -
While my earlier post contained code for selecting each cell in the
group,
VBA code works much faster if you do not select cells. And the
fact
is
that
rarely do you need to select a cell to accomplish your goal.
In this case, you may simply need to create your formula in VBA,
using
R1C1
references, assign the formula to the range of cells, then convert
each
cell
to a value like so, which doesn't select ANY cells and work very,
very
fast
With range("FormulaRange")
.FormulaR1C1 = "=rc[-1]"
For Each cc In range("FormulaRange")
cc.Formula = cc.Value
Next
End With
"Rob" wrote:
I'm trying to select a series of ranges to
1. paste a named formula I've created and then
2. remove the formula & leave the value
What I have so far is shown below, but I'm sure there must be a
better
way.
The problem seems to be that I can't use the copy function in a
multiple
range. I guess I can't paste xlValues to multiple ranges either,
so
I'm
being forced to handle each range, one at a time, which seems
rather
cumbersome.
Sub Macro1()
Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select
Selection = "=ITNBudgetFormula"
Range("G8:R45").Select
Range("G8:R45").Copy
Selection.PasteSpecial Paste:=xlValues
Range("G50:R59").Select
Range("G50:R59").Copy
Selection.PasteSpecial Paste:=xlValues
'etc for the rest of the range
End Sub