Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: VBA For Subtracting
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Mar 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA For Subtracting

    work well BUT
    can we exempt the c,e columns ?? from the calculations?

    Quote Originally Posted by DanteAmor View Post
    Put the following code in the events of your sheet

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("B2:F2")) Is Nothing Then
            If Target.Count > 1 Then Exit Sub
            If Target.Value = "" Then Exit Sub
            Range("A1").Value = Range("A1").Value - Target.Value
        End If
    End Sub
    SHEET EVENT
    Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,128
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA For Subtracting

    Quote Originally Posted by ElPerson View Post
    work well BUT
    can we exempt the c,e columns ?? from the calculations?
    Use this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("B2,D2,F2")) Is Nothing Then
            If Target.Count > 1 Then Exit Sub
            If Target.Value = "" Then Exit Sub
            Range("A1").Value = Range("A1").Value - Target.Value
        End If
    End Sub
    Regards Dante Amor

  3. #13
    New Member
    Join Date
    Mar 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA For Subtracting

    Good
    this is restricted to raw 2 only
    what to do to make it apply as long as the document go?

    i mean say down to raw 100??
    Quote Originally Posted by DanteAmor View Post
    Use this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("B2,D2,F2")) Is Nothing Then
            If Target.Count > 1 Then Exit Sub
            If Target.Value = "" Then Exit Sub
            Range("A1").Value = Range("A1").Value - Target.Value
        End If
    End Sub

  4. #14
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,128
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA For Subtracting

    Try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("B2:B100,D2:D100,F2:F100")) Is Nothing Then
            If Target.Count > 1 Then Exit Sub
            If Target.Value = "" Then Exit Sub
            Range("A1").Value = Range("A1").Value - Target.Value
        End If
    End Sub
    Regards Dante Amor

  5. #15
    New Member
    Join Date
    Mar 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA For Subtracting

    Dear Sir

    Thank you it worked fine

    Now i'm wondering can we do the same using formula??
    as you know the VBA won't work on android

    Thank you

  6. #16
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,128
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA For Subtracting

    Quote Originally Posted by ElPerson View Post
    Dear Sir

    Thank you it worked fine

    Now i'm wondering can we do the same using formula??
    as you know the VBA won't work on android

    Thank you
    I'm glad to help you. Thanks for the feedback.

    I could not try it for android.
    Regards Dante Amor

  7. #17
    New Member
    Join Date
    Mar 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA For Subtracting

    i think the formula will work in android

    Can we do the same actions with formula instead of VBA code?

  8. #18
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,128
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA For Subtracting

    Quote Originally Posted by ElPerson View Post
    i think the formula will work in android

    Can we do the same actions with formula instead of VBA code?
    No, because in a cell you can not put a value and a formula, and that is possible in VBA:

    Code:
    Range("A1").Value = Range("A1").Value - Target.Value
    Regards Dante Amor

  9. #19
    New Member
    Join Date
    Mar 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA For Subtracting

    hi
    would you please give me a VBA for the following function

    i want it to watch the column B and when i enter a value in B it multiply it by 20 and add the value to C same raw

    also monitor D and the same update value to E then F and add to G

    Thank you

  10. #20
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,697
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA For Subtracting

    Quote Originally Posted by ElPerson View Post
    hi
    would you please give me a VBA for the following function

    i want it to watch the column B and when i enter a value in B it multiply it by 20 and add the value to C same raw

    also monitor D and the same update value to E then F and add to G

    Thank you
    This appears to be a new request in which case you need to create a new thread
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

Some videos you may like

User Tag List

Tags for this Thread

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
  •