VBA show result only in cell

[iainmartin100]

Hi,

I have searched and seached the forum but cant find the answer to this most basic question.

I have a line of code which puts a formula into cell A1.
All I want to do is have the code do the formula and the result only show in cell A1.

Here's an example of the code:
Range("A1").Formula = "='C:\[Book2.xls]Sheet1'!$B$4"

If the result was 5 then I just want to see 5 in the cell rather than the forumla behind the result also (vaule only).
The reason is to reduce the size of a large excel document with hudreds of formula all doing simular things.

Many thanks

 

[VoG]

One way

With Range("A1")
    .Formula = "='C:\[Book2.xls]Sheet1'!$B$4"
    .Value = .Value
End With

 

[iainmartin100]

Thanks for the reply, do you know of any way I can do this in one line of code?

The reason being is that I have maybe 3000 forumla's to do this on so wanted to do a simple 1 line per cell script?

Thank you

 

[VoG]

Try this - Book2 must be open

Range("A1").Value = Workbooks("Book2.xls").Sheets("Sheet1").Range("B4").Value