VBA For Subtracting

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
work well BUT
can we exempt the c,e columns ?? from the calculations?

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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,224
Office Version
2007
Platform
Windows
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("[COLOR=#0000ff]B2,D2,F2[/COLOR]")) 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
 

ElPerson

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

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("[COLOR=#0000ff]B2,D2,F2[/COLOR]")) 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
 

DanteAmor

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

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("[COLOR=#0000ff]B2:B100,D2:D100,F2:F100[/COLOR]")) 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
 

ElPerson

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

DanteAmor

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

ElPerson

New Member
Joined
Mar 16, 2017
Messages
24
i think the formula will work in android

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,224
Office Version
2007
Platform
Windows
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:
[COLOR=#333333]Range("A1").Value = Range("A1").Value - Target.Value[/COLOR]
 

ElPerson

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,302
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,971
Messages
5,447,618
Members
405,458
Latest member
newbie111

This Week's Hot Topics

Top