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.

"anon" wrote:


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
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
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?)