+ Reply to Thread
Results 1 to 6 of 6

Editing formula in VBA to always find specific cell

  1. #1
    Registered User
    Join Date
    05-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Editing formula in VBA to always find specific cell

    Hello Everyone,

    I am trying to make a macro that will always return a certain rebate percentage based on a defined growth rate. I wrote the formula in its respective cell, and recorded a macro, but the worksheet itself is a template that needs to be flexible for various amounts of data. I cannot include the entire worksheet due to some sensitive information, but I have attached a file with the two columns where my problem is occuring. Also, here is the current VBA code for my macro (this is for my bigger sheet so the column numbers are different):

    Sub Rebate()
    '
    ' Rebate Macro
    '

    '
    Range("L1").Select
    ActiveCell.FormulaR1C1 = _
    "=""Rebate: ""&IF(OR(R213C11>=0.15,R213C11<=0.19),""2%"",IF(OR(R213C11>=0.2,R213C11<=0.25),""3%"",IF(R213C11>=0.26,""4%"","0%")))"
    Range("L14").Select
    End Sub


    Basically, I need something that will always refer to the last cell in the column directly to the left (in the worksheet it says 16%). How can I edit the above code to allow me to do this? If you need more specification, let me know. Thanks for all of your help.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Editing formula in VBA to always find specific cell

    The code below, will check for the last cell on column L and the place it on the formula.
    Please Login or Register  to view this content.
    Please click on the star below the posting if this was helpful. and dont forget to mark it as solved

  3. #3
    Registered User
    Join Date
    05-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Editing formula in VBA to always find specific cell

    Yep, this works. Thank you!

  4. #4
    Registered User
    Join Date
    05-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Editing formula in VBA to always find specific cell

    RaulRodriguez,

    Actually I just added a little bit more to your original coding. I now want to also show an actual dollar amount multiplied by the specified rebate percentage that I found using your code. I am pretty new to VBA, so could you take a look at this and tell me what's wrong? I basically tried to copy what you did by adding a second string to multiply my values (located in the column to the left of the growth rate). I highlighted my added code. Let me know what you think.

    Sub Rebate()

    iLastRow = Range("K65536").End(xlUp).Row
    firstrng = Range("K" & iLastRow).AddressLocal
    iNextRow = Range("I65536").End(xlUp).Row
    secstrng = Range("I" & iNextRow).AddressLocal
    Range("L3").Formula = _

    "=""Rebate: ""&IF(and(" & firstrng & ">=0.15," & firstrng & "<=0.19),""2%"",IF(and(" & firstrng & ">=0.2," & firstrng & "<=0.25),""3%"",IF(" & firstrng & ">=0.26,""4%"",""0%"")))&"" ""&""$""&IF(and(" & firstrng & ">=0.15," & firstrng & "<=0.19),"& secstrng &" * "0.02"&IF(and(" & firstrng & ">=0.2," & firstrng & "<=0.25),"& secstrng & * .03",IF(" & firstrng & ">=0.26,"& secstrng & * 0.04",""(None)"")))

    Range("B2").Select

    End Sub

    Thanks!
    Last edited by holyasian; 06-20-2012 at 10:54 AM.

  5. #5
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Editing formula in VBA to always find specific cell

    Please check the ammended code below:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Editing formula in VBA to always find specific cell

    Nice. I just had to clean up some of the quotation marks and it worked great. Thanks RaulRodriguez.
    Last edited by holyasian; 06-21-2012 at 11:17 AM.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1