Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell After Update function (Excel 2003)

    Hi

    I have a table with columns for (amongst other things) amount and tax plus a column with a drop down list with options No (default) and Yes. I'd like to be able to run some code so that if a user selects Yes the amount is reduced by tax and tax is entered in its column. I guess I also need code to reverse this if the user changes the cell back to No.

    Can anyone point me in the right direction?

    David

  2. #2
    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: Cell After Update function (Excel 2003)

    Why do you need a macro? A formula with an IF would seem to fit the need.

    Could you elaborate on your setup and what you need exactly?

    Steve

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

    Re: Cell After Update function (Excel 2003)

    How is tax calculated? Or how does "and tax is entered in its column" work?

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell After Update function (Excel 2003)

    Thanks for the prompt response.

    Let us say that cell B1 is blank and the user enters a figure of

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

    Re: Cell After Update function (Excel 2003)

    Right-click the sheet tab and select View Code.
    Enter or paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Const dblVat = 0.175
    Application.EnableEvents = False
    If Not Intersect(Range("B1:B20"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("B1:B20"), Target).Cells
    If IsNumeric(oCell) Then
    If oCell.Offset(0, 1) = "Yes" Then
    oCell.Offset(0, 2) = oCell * dblVat / (1 + dblVat)
    oCell = oCell / (1 + dblVat)
    End If
    End If
    Next oCell
    End If
    If Not Intersect(Range("C1:C20"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("C1:C20"), Target).Cells
    If IsNumeric(oCell.Offset(0, -1)) Then
    If oCell.Offset = "Yes" Then
    oCell.Offset(0, 1) = oCell.Offset(0, -1) * dblVat / (1 + dblVat)
    oCell.Offset(0, -1) = oCell.Offset(0, -1) / (1 + dblVat)
    Else
    oCell.Offset(0, -1) = oCell.Offset(0, -1) + oCell.Offset(0, 1)
    oCell.Offset(0, 1).ClearContents
    End If
    End If
    Next oCell
    End If
    Application.EnableEvents = True
    End Sub

    Adjust the ranges as needed. See attached sample workbook.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell After Update function (Excel 2003)

    I am once again filled with admiration. Exactly what I was looking for. It will take me a little while to get it into my existing spreadsheet but the sample proves it works.

    I'd love to know what you do for a living. Man, you're always there within minutes with awesome responses.

    Regards
    David

Posting Permissions

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