What is the general way of storing a formula as a String and Using the String again? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Tomorrow, 02:44
Joined
Mar 22, 2009
Messages
771
The Formula is:

=Sheet2!RC[11]&"" ""&Sheet2!RC[10]

How do I have to store it as a string in a cell?

'=Sheet2!RC[11]&"" ""&Sheet2!RC[10] ?
or
"=Sheet2!RC[11]&"" ""&Sheet2!RC[10] ?
or
"=Sheet2!RC[11]&"" ""&Sheet2!RC[10]" ?
or
=Sheet2!RC[11]&"" ""&Sheet2!RC[10] ? (As a Direct formula only)

How to use the stored string as a formula again (Throug VBA) ?

SomeSheet.SomeColumn.SomeRange.FormulaR1C1 = cell.value ? (Cell.value holds the formula as String)
or
SomeSheet.SomeColumn.SomeRange.Formula = cell.value ?
or
SomeSheet.SomeColumn.SomeRange.Formula = cell.formula ? (This method works for non R1C1 formulas but I don't want to see an unnecessary error field in my mapping file)
 

Rx_

Nothing In Moderation
Local time
Today, 15:14
Joined
Oct 22, 2009
Messages
2,803
The single quote in front will store anthing that follows as a string.
Since 'reuse' is mentioned for another cell, it brings up the question about your goals and intentions.
If you could give more information about your goals, there is probably a better way to accomplish this.
Feel free to create and attach a small spreadsheet with a column of notes that describes the objective.
 

RayH

Registered User.
Local time
Today, 14:14
Joined
Jun 24, 2003
Messages
132
As I understand it, you want to be able to store the RC formula in the cell in a VBA string variable. Is that right?

This will do that:
x=SomeSheet.SomeRange.FormulaR1C1
 

Rx_

Nothing In Moderation
Local time
Today, 15:14
Joined
Oct 22, 2009
Messages
2,803
objxl is a variable that is equal to Excel.Application
objxl.Range("C4").Select
objxl.ActiveCell.FormulaR1C1 = "=SUBTOTAL(109, R[2]C[10]:R[" & lngMaxRecordCount + 2 & "]C[10])"
OR They can be constructed to use variables for a recordcount or within a loop that increments a counter varable

objxl.Range("G2").Select
objxl.ActiveCell.FormulaR1C1 = "= AGGREGATE(4,3,R[4]C:R[" & lngMaxRecordCount + 4 & "]C)"

Tell us more about the objective.
 

Users who are viewing this thread

Top Bottom