Results 1 to 6 of 6

Thread: automatic regeneration of formulas once information in cell is overwritten
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Location
    Australia, Sydney
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default automatic regeneration of formulas once information in cell is overwritten

    It is like what was done in this thread : http://www.mrexcel.com/forum/showthread.php?t=432212

    However I want to do the same except with formulas, but I don't want the formulas visible like in the post thread above, where it shows "Type prosthesis here". I only want the formulas to regenerate if what is orignally in the cell is overwritten then deleted.

    Example

    column
    -----A
    row 1--=B2*c3

    formula in cell A1 = B2*C3 is overwritten intentionally with digit 3, then the user discovers this information is wrong. The user then deletes the digit 3 and then in cell A1 the formula = B2*C3 automatically comes back.

    Thanks

  2. #2
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automatic regeneration of formulas once information in cell is overwritten

    Something like this??

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Text = "" And Target.Address = "$A$1" Then Target.Formula = "=B2*C3"
    End Sub
    Paste to the sheet level in the VBE
    .select, overused and inefficient.

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    Australia, Sydney
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automatic regeneration of formulas once information in cell is overwritten

    THankyou sooo much.. But how do I merge it with the formula in the thread above?

    That macro is based on cases. How do I change merge your formula with it?

    Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target = "" Then
    Select Case Target.Address
    Case "$F$45": Target = "Type Prosthesis Here"
    Case "$F$46": Target = "Type Prosthesis Here"
    Case "$F$47": Target = "Enter Item here & amount in total charge"
    'etc
    Case Else:
    End Select
    End If
    Application.EnableEvents = True
    End Sub
    Last edited by uniquo; Dec 1st, 2009 at 06:49 PM. Reason: add extra word

  4. #4
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automatic regeneration of formulas once information in cell is overwritten

    Code:
    Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target = "" Then
    Select Case Target.Address
    Case "$F$45" Target = "Type Prosthesis Here"
    Case "$F$46" Target = "Type Prosthesis Here"
    Case "$F$47" Target = "Enter Item here & amount in total charge"
    Case "$A$1" Target.Formula = "=B2*C3" '<----New entry
    
    'etc
    Case Else
    End Select
    End If
    Application.EnableEvents = True
    End Sub
    .select, overused and inefficient.

  5. #5
    Board Regular
    Join Date
    Jul 2008
    Location
    Australia, Sydney
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automatic regeneration of formulas once information in cell is overwritten

    Is the ' between C3" and < a typo or intentionally put there?

  6. #6
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automatic regeneration of formulas once information in cell is overwritten

    It's a comment (preceded with a ') this tells the code that it is purely for your reference and will not effect the code. I put it in there to show you what I added, you can leave it in or take it out, it makes no difference to the code. If you take it out you need to take out everything after it too.
    .select, overused and inefficient.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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