Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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
    28,535

    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
    East Sussex
    Posts
    25,272

    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.

  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
    East Sussex
    Posts
    25,272

    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"","""")"

  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

Page 1 of 2 12 LastLast

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