Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing cell formula is other cell changes (Excel 2000)

    Hi Lounge,
    I am looking for help on an issue that I have with being able to override a cell that had a formula in it and back calculate by adding a formula to a cell that use to reference a cell,

    In my example,

    I am calculating the amount of cement needed for a job,

    There is 2 ways to calculate it,
    The slurry wet amount measured in m

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing cell formula is other cell changes (Excel 2000)

    I think you'd have to use the Worksheet_Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("G75"), Target) Is Nothing Then
    Application.EnableEvents = False
    Range("I75").Formula = "=G75/D75"
    Application.EnableEvents = True
    ElseIf Not Intersect(Range("I75"), Target) Is Nothing Then
    Application.EnableEvents = False
    Range("G75").Formula = "=I75*D75"
    Application.EnableEvents = True
    End If
    End Sub

    It is not clear to me, however, if and when you would want to restore the original formula =I58 in G75.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing cell formula is other cell changes (Excel 2000)

    Thanks Hans, Quick reply,

    I dont need to cange the G75 back to =I58 If a change is made then there is no going back.

    It work great,

    On question, If I already have worksheet change code present behind the worksheet, do i simply just add the code you provided along with the other code in the same sub?

    I am not sure how to name the sub differently then Private Sub Worksheet_Change(ByVal Target As Range) so I am assuming i just add it to the same sub. Let me know if I am incorrect.
    Thanks again,

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing cell formula is other cell changes (Excel 2000)

    You can add multiple code, but the comparisons and intersects can make the code sluggish when you edit the cells, since the event is called everytime that a cell is changed.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing cell formula is other cell changes (Excel 2000)

    Hi Steve,

    Any way to get around this by break it up?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing cell formula is other cell changes (Excel 2000)

    I don't think that'll make a difference. On most recent PCs, you won't notice the overhead, by the way - you'd have to write quite a bit of code before it would really slow down Excel. Still, you should only include code you really need.

Posting Permissions

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