Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Named Range to be used with Target.Address

    Hi all,

    Please see the below code, I would like to make this easier to read than how it is now and also this would help if the users needs to add rows/columns:

    I would like to replace the L29, H37, E40 etc with a range called "Change" and whenver one of the cells is touched the calculation carries out.

    I also need this to run thourhg itself twice due to a few different factors, is there a way to loop it twice?

    Is this possible?

    Thanks

    'If Range("E29") = "No" Then
    'If Target.Address = "$L$29" Or Target.Address = "$H$37" Or Target.Address = "$E$40" Or Target.Address = "$L$30" Or Target.Address = "$L$28" Or Target.Address = "$E$30" Or Target.Address = "$E$29" Or Target.Address = "$H$38" Or Target.Address = "$H$39" Or Target.Address = "$G$37" Or Target.Address = "$L$20" Then
    'Range("J36") = Range("L29").Value
    'End If
    'End If

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    If Not Intersect(Target, Range("Change")) Is nothing Then
    Range("J36") = Range("L29").Value
    Range("J36") = Range("L29").Value
    end if
    Not really sure why you need it to do that twice but if this code is inside a change event, it's going to get triggered repeatedly anyway...
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    You can stop the repeated Event calls with the Application.EnableEvents property.
    Code:
    If Not Intersect(Target, Range("Change")) Is nothing Then
      Application.EnableEvents = False
      Range("J36") = Range("L29").Value
      Range("J36") = Range("L29").Value
      Application.EnableEvents = True
    End if
    Of course this doesn't explain why it is in the code twice.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    RG,
    I know, but we don't know if this is a change event or whether it is the triggering of other event code that necessitates calling it twice, so I was awaiting further details.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the above, the reson why i wanted this is there are 2 fields each wich have a yes or no drop down box, when one of them is changed a calculation needs to run, and when the other box is chnaged another calculation. The amount i.e. profit will be changed depending on whaty one changes so i need to have it re-run for the Yes section even if it doesnt change oer get touched.

    Thanks for your help.

Posting Permissions

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