Evaluating formulas when a template is brought into a worksheet...

[Guest]

Hi.

I have a template that has a bunch of formulas in it. It is stored as a
template, and then programatically brought into a workbook.

Some of the formulas in the template are intended to perform lookups against
named ranges in the worksheet that it is brought into. However, the formulas
do not evaluate when the template is brought in.

At first, I thought that just forcing a recalc would take care of it, but it
does not.

If I put this sbEval in, the formulas evaluate, and everything's fine:

***********
Sub sbEval()
Dim c As Range
Dim i As Integer
i = 1
For Each c In Range("rgRateLookup").Cells
c.Formula = c.Formula
Next c
End sub
*************

Is there a different better way to do that? It's not a lot of cells, this
will work. It's just that I thought recalc would handle it, and it didn't.

Thanks.

 

[Guest]

The recalc does not work because when the formulas are brought in they are
entered in a way that Excel does not recognize them as formulas. Your

c.Formula=c.Formula

basically re-enters the formulas a second time. The second time you are
explicitly telling Excel that, Hey, this is a formula.

You can get the same result by going to each cell, touch F2 and then touch
ENTER. Your way is, of course, much better.


By the way, the same thing can sometimes happen if you import hyperlinks.

 

[Dave Peterson]

I'm not sure why you're having trouble, but I would think that this would be
quicker:

activesheet.range("rgRateLookup").replace what:="=", Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Same as
selecting the range
edit|replace
what: = (equal sign)
with: =
replace all

 

[Guest]

okay, thanks for the explanation and suggestion, guys.

either way will work fine.. it's only 8 cells in the range in the one
template, and maybe 20 or a little more in the other.

Thanks.

 

[Guest]

ahh, I see I didn't even need the integer i .

I started to do something slightyly different, and then missed taking that
back out.