Results 1 to 6 of 6

Thread: Macro to add a formula in a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Location
    Bakersfield, CA
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to add a formula in a cell

    Hello All,

    Here is my situation, I have a macro that when a certain item is column "D". It formats several other columns in the same row that the information is being added. My situation is that part of the formatting is erasing information. The problem is that in column "AE" there is a specific formula. The following formula is added to the cell

    =IF(AD3="N/A",EDATE(AC3,12),EDATE(AD3,12))

    So my questions is how do i code so the above formula is inputed in column AE. Note that "AD3" is just where i copied the formula from. The number of the row is currently stored in Target.Row so it changes based on which row the formula is inputted.

    Thanks for any help.

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,784
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to add a formula in a cell

    .
    Sheets("Sheet1").Range("F4").Value = "=IF(AD3=""N/A"",EDATE(AC3,12),EDATE(AD3,12))"

    Edit the sheet name and the cell value as needed.

    Note in the formula ... where there originally was one quotation mark .. there are now two.

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Macro to add a formula in a cell

    Quote Originally Posted by zoog25 View Post
    .. how do i code so the above formula is inputed in column AE. Note that "AD3" is just where i copied the formula from. The number of the row is currently stored in Target.Row so it changes based on which row the formula is inputted.
    Try
    Code:
    Range("AE" & Target.Row).Formula = Replace("=IF(AD#=""N/A"",EDATE(AC#,12),EDATE(AD#,12))", "#", Target.Row)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Location
    Bakersfield, CA
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to add a formula in a cell

    Thank you Logit and Peter for the help. I believe the Peter's replace method is what i was looking for so thank you.

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,784
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to add a formula in a cell

    Glad to help.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Macro to add a formula in a cell

    Quote Originally Posted by zoog25 View Post
    Thank you Logit and Peter for the help. I believe the Peter's replace method is what i was looking for so thank you.
    You're welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •