Results 1 to 10 of 10

Set a cells Formula using VBA?

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 ?...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    87

    Default Set a cells Formula using VBA?

    How do you set a cells formula using VBA?

  2. #2
    Board Regular
    Join Date
    Jun 2003
    Location
    Delaware
    Posts
    491

    Default Re: Set a cells Formula using VBA?

    You could use application.worksheetfunction

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    Sacramento
    Posts
    111

    Default Re: Set a cells Formula using VBA?

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

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    26,841

    Default Re: Set a cells Formula using VBA?

    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

  5. #5
    New Member
    Join Date
    Jan 2009
    Posts
    1

    Default Re: Set a cells Formula using VBA?

    '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])"

  6. #6
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Question Re: Set a cells Formula using VBA?

    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

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,361

    Default Re: Set a cells Formula using VBA?

    Code:
    Range("F8").Formula = "=IF(ISNUMBER(C8,""DefaultText"","""")"
    You basically double each set of quotation marks within the formula string.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  8. #8
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Default Re: Set a cells Formula using VBA?

    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"

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    ex machina
    Posts
    21,361

    Default Re: Set a cells Formula using VBA?

    I misread your formula, which has a missing parenthesis:
    Code:
    Range("F10").Formula = "=IF(ISNUMBER(C10),""DefaultText"","""")"
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  10. #10
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Smile Re: Set a cells Formula using VBA?

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

    shawthingz

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com