Excel 2007 Recognize Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TKS_Mark - 12 Apr 2008 23:04 GMT

I've modified an existing macro that copies one range to another by pasting
values. The source range has formulas that use CONCATENATE to build a
formula by looking up values in several cells. That part works ok, but when
I paste it as values, I need to edit each cell and change the = to = (in
other words, fool Excel into thinking I've just entered this brilliant
formula on my own. Otherwise, Excel just shows the pasted formula instead of
the results). The code below did it on a range that is some distance away
from the original range. This time, I just want to do it to a specific range
called 'AZInputs[TestPaste]'. What change would I need to make to the code
below in order to make this work?

With myRngToCopy
.Copy
With .Offset(0, -12)
.PasteSpecial Paste:=xlPasteValues
'Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With

Reply to this Message

anon - 13 Apr 2008 00:36 GMT

Ok. You code does this;

Copies a defined range;

> With myRngToCopy

MyRngToCopy is tha name of a range. If you wanted to copy another
range you could change this line to;

Dim thisrange
thisrange = activesheet.range("a1:z10")
with this range

Then it copies the range

> .Copy

Goes to where it wants to paste it (offset 0 rows and -12 columns from
the activecell)

> With .Offset(0, -12)

Pastes it

> .PasteSpecial Paste:=xlPasteValues

Then runs the code to change the values;

> 'Selection.Style = "Calculation"
> With .Resize(, 2) 'same number of rows, but two columns
[quoted text clipped - 8 lines]
> End With
> End With

If you have already copied and pasted the range you want to change and
simply want to run the value changing part of the code on this range
you simply need;

with activesheet.range(AZInputs[TestPaste])
'Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With

However I am bemused as to why you would need to change the =. I have
tried copying/pasting some test formula and have no problem with it
showing immediately as a value and not as a formula where i would need
to replace the = to make it work. (Have you tried copying/pasting
manually to see the results?)

Reply to this Message

TKS_Mark - 13 Apr 2008 21:49 GMT

Anon,

Well, I've been exerimenting with this lookup function and pulling it's
references from neighboring cells to build my formula. Previously, it never
worked. It would turn the internal indirect part of it into text instead of
the reference I needed it to be. Today it's working. I'm not sure what I
was doing wrong before, but I'm glad it works. Now I don't even need a
macro. Thanks for your help with the bemusement statement.

Mark.

> Ok. You code does this;
>
[quoted text clipped - 60 lines]
> to replace the = to make it work. (Have you tried copying/pasting
> manually to see the results?)

Reply to this Message