newbie question Re: pasted formula need to hit enter to calc

[techseek]

Hello
I have a sub which creates a named range in one sheet then it copies a formula from another workbook/sheet to that range
but the cells show text. I then have to hit enter on each cell to show the calc value. I tried changing the range cell format to "general" - no effect
what am i doing wrong?
thank you

 

[Googleplexity]

techseek,

It has to do with how you are copying the cell vaules in your vba module. Paste your code so I can see how your doing it. If your using .Copy for instance you need to tell excel to paste as a formula, if your using properties then you might try something using .FormulaR1C1 either way lets see it and I can help you out

 

[SkipVought]

Please post your code.

We cannot answer questions like this intelligently, unless we know what code you are executing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[techseek]

Hi
thx for replying
I had to wait to get in the office today to respond
here is the code i'm using

Sheets("CustodyHoldings").Select
Range("E2").Select
Selection.Copy
Workbooks(Current).Activate
MsgBox "current is " & Current
MsgBox "Yesterday is " & yesterday
Workbooks(Current).Sheets("CustodyHoldings").[Pi].Formula = Workbooks(yesterday).Sheets("CustodyHoldings").Range("E2").Formula
Range("PI").Select
Selection.NumberFormat = "general"

Thank you

 

[SkipVought]

You nowhere PASTE anything!!!

Rather, you are assigning a formula.

So FORMAT the cell, BEFORE you assign the value.

With Workbooks(Current)
    .activate
    MsgBox "current is " & Current
    MsgBox "Yesterday is " & yesterday
   With .Sheets("CustodyHoldings").[Pi]
      .NumberFormat = "general"
      .Formula = Workbooks(yesterday).Sheets("CustodyHoldings").Range("E2").Formula
       .Select
   end with
End with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[techseek]

thank you SV
but now i'm left wondering if i should be pasting instead of doing the formula assignment
I'd like to do it the way you would do it
As it's really my first time trying to do VBA programming
Thank you

 

[SkipVought]

Is there a problem with the code as posted?

Is there more to your question than is apparent in this thread?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[techseek]

hello
I was asking if it is better to paste the formulas as opposed to assigning like i did

 

[SkipVought]

It depends on what you want to accomplish. Thee is no blanket "better" method.

Pasting, can accomplish transferring formulas, values and formats in one operation. You can do the same by assignment, by explicitly specifying each, but it is not a simple as pasting.

I use either as the circumstance dictates.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]