Set a cells Formula using VBA?

[rkcim]

How do you set a cells formula using VBA?

 

[north19701]

You could use application.worksheetfunction

 

[Wayne L.]

Try the Formula property or FormulaR1C1 property (check out Help for explanations on how to use them).

 

[Smitty]

A relatively easy way to experiment with this is to record a new macro and then in the cell(s) that contains your formula(s), hit F2, then enter to record the formula.

HTH,

Smitty

 

[sheeja Paul]

'Select the cell on which you want to put formula
Cells(myrow, myCol).Select

'My equation is D7=C7 * B7 * A7
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]*RC[-3])"

 

[shawthingz]

I'm stuggling with the Formula / FormulaR1C1 functions...


Here's a Use Case (hope it makes sense!):


Initially, the formula in $F$8 is " =IF(ISNUMBER(C8,"DefaultText","") "

C8 = "" so the formula evaluates to "" (e.g. BLANK)

C8 is set to 2 (based on the value set in $A$1) so the formula now automatically evaluates to "DefaultText"

The user now overrides the formula (using drop-down data validation list) so the cell content now becomes "MyUserText"

The user now changes the value of $A$1 in such a way that the value of C8 becomes "" again

When / if this happens, I need to be able to restore the original formula into $F$8

I've tried various things but I keep getting run-time errors - anyone have any ideas / tips?

Thx

shawthingz

 

[RoryA]

Range("F8").Formula = "=IF(ISNUMBER(C8,""DefaultText"","""")"

You basically double each set of quotation marks within the formula string.

 

[shawthingz]

Thanks for the tip rorya,

I plugged this code snippet straight in, but unfortunately I'm still getting a run-time error :


Run-time error 1004:

Application-defined or object-defined error


If it helps at all, here's the surrounding code that runs when the numeric value in $A$1 is changed (BTW, I'm using 2010 edition):

ActiveSheet.Unprotect Password:="a.password"

'Clean-up any pre-existing cell content if NumberOfUsersInBatch is reduced
Cell_Range_To_Clear = "D" & NumberOfUsersInBatch + 7 & ":E106"
Range(Cell_Range_To_Clear) = ""

'Unlock appropriate cells based on NumberOfUsersInBatch
Cell_Range_To_Unprotect = "D7" & ":F" & NumberOfUsersInBatch + 7 - 1
Range(Cell_Range_To_Unprotect).Select
Selection.Locked = False
Selection.FormulaHidden = False

'IDEALLY, APPLY THE DEFAULT FORMULA TO ALL CELLS AT ONCE
'IF NOT POSSIBLE, USE A FOR / WHILE LOOP
Cell_Range_To_Reset = "F" & NumberOfAgentsInBatch + 7 & ":F106"
Range("F10").Select
Range("F10").Formula = "=IF(ISNUMBER(C10,""DefaultText"","""")"

'Lock all other cells
Cell_Range_To_Protect = "D" & NumberOfAgentsInBatch + 7 & ":F106"
Range(Cell_Range_To_Protect).Select
Selection.Locked = "True"
Selection.FormulaHidden = "True"

ActiveSheet.Protect Password:="a.password"

 

[RoryA]

I misread your formula, which has a missing parenthesis:

Range("F10").Formula = "=IF(ISNUMBER(C10),""DefaultText"","""")"

 

[shawthingz]

Many thanks for your "eagle eyes", it's now working perfectly!

shawthingz