+ Reply to Thread
Results 1 to 10 of 10

Add formula thru VBA

Hybrid View

  1. 03-12-2013, 09:15 AM #1
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Add formula thru VBA

    Hello:

    I am using below VBA code successfully.

    Sub CurrentMonth_CopyPaste_Sales()

Dim i As Long
i = Range("B112").Value
Range("F" & i).Copy
Range("F" & i).PasteSpecial xlPasteValues
'NumberFormat = "0.00"
    
End Sub
    I want to add the formula in the next row : =IF($Ax=$K$40,$L$19,0)
    where x in $Ax would be 1 + the value in cell B112
    Example if Cell value in B112 = 50, then the formula would be =IF($A51=$K$40,$L$19,0)

    I hope this is clear and let me know if you have any questions.

    Thanks

    Riz
    Last edited by arlu1201; 03-12-2013 at 01:20 PM. Reason: Code tags should be put for the 1st post.
    Reply With QuoteRegister To Reply
  2. 03-12-2013, 09:30 AM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add formula thru VBA

    Maybe:

    Sub rizmominA()
Dim i As Long
i = Range("B112").Value
Range("F" & i).Value = Range("F" & i).Value
Range("F" & i).NumberFormat = "0.00"
Range("F" & i + 1).Formula = "=IF($A" & i & "=$K$40,$L$19,0)"
End Sub
    Reply With QuoteRegister To Reply
  3. 03-12-2013, 09:52 AM #3
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Add formula thru VBA

    Hi John:

    Yes this works..
    Thank so so much..

    Riz
    Reply With QuoteRegister To Reply
  4. 03-12-2013, 09:56 AM #4
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add formula thru VBA

    You're welcome. Glad to hear that and thanks for the feedback.
    Reply With QuoteRegister To Reply
  5. 03-12-2013, 10:26 AM #5
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Add formula thru VBA

    rizmomin,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  6. 03-12-2013, 10:31 AM #6
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Add formula thru VBA

    Hello arlu201:

    Sorry, i got it..this wont happen again.
    Now i know how to tag the code.

    Riz
    Reply With QuoteRegister To Reply
  7. 03-12-2013, 10:33 AM #7
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Add formula thru VBA

    Hi John:

    Can you help me add 1 more formula.

    Want to add 

    Range("G" & i + 1).Formula =IF(Fx=0,"",Fx-Ex)
    where x = 1 + value in cell B112

    Thanks

    Riz
    Reply With QuoteRegister To Reply
  8. 03-12-2013, 10:54 AM #8
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add formula thru VBA

    Maybe:

    Sub rizmominA()
Dim i As Long
i = Range("B112").Value
Range("F" & i).Value = Range("F" & i).Value
Range("F" & i).NumberFormat = "0.00"
Range("F" & i + 1).Formula = "=IF($A" & i & "=$K$40,$L$19,0)"
Range("G" & i + 1).Formula = "=IF(F" & i + 1 & "=0,"""",F" & i + 1 & "-E" & i + 1 & ")"
End Sub
    Reply With QuoteRegister To Reply
  9. 03-12-2013, 11:38 AM #9
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Add formula thru VBA

    Hi John:

    Thanks a lot for your help...it works.

    Riz
    Reply With QuoteRegister To Reply
  10. 03-12-2013, 11:58 AM #10
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add formula thru VBA

    Glad to hear and thanks for the feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1