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
 
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.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
i think the formula will work in android

Can we do the same actions with formula instead of VBA code?
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top