You could use application.worksheetfunction
This is a discussion on Set a cells Formula using VBA? within the Excel Questions forums, part of the Question Forums category; How do you set a cells formula using VBA ?...
How do you set a cells formula using VBA?
You could use application.worksheetfunction
Try the Formula property or FormulaR1C1 property (check out Help for explanations on how to use them).
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
'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])"
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
You basically double each set of quotation marks within the formula string.Code:Range("F8").Formula = "=IF(ISNUMBER(C8,""DefaultText"","""")"
Rory
ʅ_(ツ)_ʃ
__ |
__ |
__/ \_
Add-ins for posting screenshots of ranges - see bottom of the page (Win & Mac)
Posting guidelines | Forum rules | FAQs | Post code in code tags
ExcelMatters Microsoft MVP - Excel
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"
I misread your formula, which has a missing parenthesis:
Code:Range("F10").Formula = "=IF(ISNUMBER(C10),""DefaultText"","""")"
Rory
ʅ_(ツ)_ʃ
__ |
__ |
__/ \_
Add-ins for posting screenshots of ranges - see bottom of the page (Win & Mac)
Posting guidelines | Forum rules | FAQs | Post code in code tags
ExcelMatters Microsoft MVP - Excel
Many thanks for your "eagle eyes", it's now working perfectly!
shawthingz
Like this thread? Share it with others