VBA For Subtracting

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
Hi
I need a VBA code for Subtracting every cell in a column from the first cell in the same column

Thank you
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,745
Office Version
2007
Platform
Windows
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
 

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
Which column should this work??
Thank you
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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
you didn't specify column, but this is set for B
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,745
Office Version
2007
Platform
Windows
Which column should this work??
Thank you
In cell B1 your first value, from B2 down your next values

 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,745
Office Version
2007
Platform
Windows
Try this

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

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
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
Try this

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,745
Office Version
2007
Platform
Windows
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.
 

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,745
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,318
Messages
5,467,907
Members
406,558
Latest member
MattJC7

This Week's Hot Topics

Top