+ Reply to Thread
Results 1 to 9 of 9

Worksheet change event to Formulate Cells for multiple Ranges

  1. #1
    Forum Contributor
    Join Date
    10-30-2010
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2010
    Posts
    100

    Worksheet change event to Formulate Cells for multiple Ranges

    How would one go about setting this up so that if I enter a number in the L column, columns M and N in the same row will automatically be formulas. Likewise if I enter a number in the M column, columns L and N for that particular row will automatically change to formulas.

    For example in detail I enter the value $0.25 in cell L7. The formula in M7 should be "=IF(L7<>"",L7*$L$2,"")", and N7 "=IF(M7<>"",M7/$M$4,0)".
    If I enter the value $50 in say cell M8, the formula in L8 should be "=IF(M8<>"",M8/$L$2,"")", and N8 "=IF(M8<>"",M8/$M$4,0)".
    and finally..
    If I enter the value 5% in cell N7. The formula in L7 should be "=IF(M7<>"",M7/$L$2,"")", and M7 "=IF(N7<>"",N7*$M$4,"")".

    I need the change events to work relatively within each row. From Rows 7 to 13.

    I've attached a worksheet to kind of show what I'm looking at.

    Thanks in advance I hope I was clear enough!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    Try This

    Please Login or Register  to view this content.
    edit: slight simplification of code
    Last edited by Mallycat; 04-15-2012 at 02:04 AM.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,108

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    @ Mallycat,

    Nice work Mallycat! Waht is?
    Chr(34)

  4. #4
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    Chr(34) is the code to create a quote mark "

    When building a formula in VBA that you want to add to a cell in a sheet, you can't add " in the formula, as this is used by the VBA code itself, so you have to use chr(34) everywhere you want a " to appear in the final formula.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,108

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    @ Mallycat,

    Thank you for clarifying that for me, however, I have changed it with the ", and it works fine! I think you could do this quite safely when one uses the Ampersand. It might be an entirely different case when a " is required without the Ampersand.

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    Thanks. I didn't know that.

  7. #7
    Forum Contributor
    Join Date
    10-30-2010
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    Man you guys are great thanks for all your help! Mallycat's version works perfect you are GENIUS! When I tried Winon's version of the code I couldn't get it to work though? Winon are you sure your code worked for you maybe I'm doing something wrong?

    Either way I appreciate both of you thanks!

  8. #8
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    Hey trickyricky. I have lots of friends in the USA. None in Kansas though, but I could always click the heels of my red shoes together, and wish for something special. :-)

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,108

    Re: Worksheet change event to Formulate Cells for multiple Ranges

    I am so sorry guys,

    Mallycat's Code is great, but the Char(34) could be replaced with """ instead of just "

    I checked out your concern trickyricky and, yes, you are right. The amended Code should look like this:

    Please Login or Register  to view this content.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

+ 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