Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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

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

    Default VBA For Subtracting

    Hi
    I need a VBA code for Subtracting every cell in a column from the first cell in the same column

    Thank you

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

    Default Re: VBA For Subtracting

    Try this

    Code:
    Sub Subtracting()
        res = Range("B1")
        For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
            res = res - Cells(i, "B")
        Next
        MsgBox "Result = " & res
    End Sub
    Regards Dante Amor

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

    Default Re: VBA For Subtracting

    Which column should this work??
    Thank you
    Quote Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub Subtracting()
        res = Range("B1")
        For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
            res = res - Cells(i, "B")
        Next
        MsgBox "Result = " & res
    End Sub

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

    Default Re: VBA For Subtracting

    you didn't specify column, but this is set for B
    • 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

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

    Default Re: VBA For Subtracting

    Quote Originally Posted by ElPerson View Post
    Which column should this work??
    Thank you
    In cell B1 your first value, from B2 down your next values

    Last edited by DanteAmor; May 8th, 2019 at 01:56 PM. Reason: img example
    Regards Dante Amor

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

    Default Re: VBA For Subtracting

    Try this

    Code:
    Sub Subtracting_2()
        MsgBox "Result = " & Range("B1") - WorksheetFunction.Sum(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row))
    End Sub
    Regards Dante Amor

  7. #7
    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
    see the attached screenshot
    i want every time i enter a value in the cells of the column B,D,F it substracted from a specific number say 1633 and the result updated in the A1 cell.
    thank you
    https://ibb.co/p0gqwHT
    Quote Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub Subtracting_2()
        MsgBox "Result = " & Range("B1") - WorksheetFunction.Sum(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row))
    End Sub

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,121
    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
    see the attached screenshot
    i want every time i enter a value in the cells of the column B,D,F it substracted from a specific number say 1633 and the result updated in the A1 cell.
    thank you
    https://ibb.co/p0gqwHT

    I do not understand. In cell A1 you have the initial value and in that same cell you want to write the result?

    Your original request does not say much about what you need now.


    I need a VBA code for Subtracting every cell in a column from the first cell in the same column

    You could explain with your words what you have in A1 and how it will change each time you capture a data, explain with several examples.
    Regards Dante Amor

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

    Default Re: VBA For Subtracting

    Excuse me , you are right, i changed my mind regarding what i want
    NOW
    i want the A1 cell to be updated every time a value in the cells of the column B,D,F is updated,
    first i assign the starting value that we will subtract from it and in this case will be 1500,
    then we start with b2 have 10 and d2 have 20 and f2 have 5
    i want excel when the value in b2 inserted it subtracted from the 1500 and enter the result to A1 "1490" then when d2 value inserted 20 it subtract it from the A1 value "1490-20" > "1470 in A1"
    so A1 now have 1470, next once the f2 value updated 5 the excel subtract that from the new A1 value "1470-5" and update the A1 with the result 1465.

    I hope the idea is clear now

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

    Default Re: VBA For Subtracting

    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.
    Regards Dante Amor

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
  •