New to VBA - want only output in excel worksheet

[Navin Agrawal]

Hello friends,

I have created an excel file for some automated analysis....

The problem I am facing is that there are far too many formulae in far too many cells....

A friend on this forum suggested I should try coding the formulae in VBA to make it easier, the file lighter and processing much faster....

I understand that the formulae can be written in the macro for example:

Sub ADD()

Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[1]"

End Sub

But when I do this, the formula is showing in the cell and this does not help in anyway.... because the formula will keep getting typed in every cell and the file will become heavy.... it is the same as typing the formulae in the excel sheet....

I want only the output to show in the excel sheet.... so that the file become light...

Can someone help pls.....

[:) Sixthsense :)]

May be this?

Please Login or Register  to view this content.

[bakerman2]

If you want the value instead of formula.

Please Login or Register  to view this content.

[Navin Agrawal]

If you want the value instead of formula.

Please Login or Register  to view this content.

Thanks for your revert.... Appreciate it.... A couple of things:

Will this keep my file light?

How do I do this for a whole range where cells have different formulae...

Lemme explain: Cells Q303:JZ602 have different formaulae in random cells.... all cells do not have formulae....

firstly I want to key in formulae in these random cells (which I will do in the VBA).... but I want only the output to be displayed in the excel sheet.... so do I have to enter .value = .value after each formula in the VBA?

Once I have done this, I want this block of Q303:KZ602 copied down 200 times.... so that the VBA internally copies down this block 200 times but only outputs are shown in the excel sheet....

Can u help on This?

[bakerman2]

Q303:JZ602

That's about 81000 cells.
How are you gonna program that in VBA ????

[Navin Agrawal]

That's about 81000 cells.
How are you gonna program that in VBA ????

basically the formulae keep getting copied down..... the formulae are in only 15000 cells with just about 100 odd unique formulae.... this i will manage... no worries on that....

[Navin Agrawal]

If you want the value instead of formula.

Please Login or Register  to view this content.

Will this keep my file light?

How do I do this for a whole range where cells have different formulae...

Lemme explain: Cells Q303:JZ602 have different formaulae in random cells.... all cells do not have formulae....

firstly I want to key in formulae in these random cells (which I will do in the VBA).... but I want only the output to be displayed in the excel sheet.... so do I have to enter .value = .value after each formula in the VBA?

Once I have done this, I want this block of Q303:KZ602 copied down 200 times.... so that the VBA internally copies down this block 200 times but only outputs are shown in the excel sheet....

Can u help on This?

[bakerman2]

Every formula you put in your sheet with VBA has to be calculated first before you can 'fix' it's output.
I don't see the advantage you're gonna get by doing it this way.
Formulas are usually faster then VBA so it's no help calculating everything in VBA and pasting to worksheet.
Maybe your formulas can be enhanced so they calculate faster.

[Navin Agrawal]

What do you suggest I do in that case?

[bakerman2]

There are some real wizards in Formulas & Functions forum so you may want to have them take a look at your formulas.

[Navin Agrawal]

thanks for your suggestion... do let me know if u can think of a solution