sum values based in specific column based on specific number

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hello
i search way to sum the values in the lastrow is empty if is the sum = 1000 then sum and show in the lastrow is empty it be like this
col a14 = total and b14=1000 but if the summing value >or < than 1000 then show message you can't summing and exit sub if is possible the code into worksheet change event is better
report.xlsm
AB
1CODEQUANTITY
2AA100
3AA1101
4AA1101
5AA2102
6BB1100
7BB1100
8BB2101
9BB3102
10CC20
11CC20
12CC130
13CC250
sheet1

thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
HI, yinkajewole what i meant when sum dynamic from b2:b13 for instance i specify constrain the value is =1000 in b14 total =1000 is ok but if b14= 900 or any value > or < than 1000 it supposing message box you can't sum values because less or more than 1000 and exit sub
i hope this help
 
Upvote 0
HI, yinkajewole what i meant when sum dynamic from b2:b13 for instance i specify constrain the value is =1000 in b14 total =1000 is ok but if b14= 900 or any value > or < than 1000 it supposing message box you can't sum values because less or more than 1000 and exit sub
i hope this help

I wish to help you but I'm just not getting your message...
 
Upvote 0
honestly i no know what exactly what you want i think i did explained my request in post #3 if have in your mind something you want to know please inform me maybe i can make clear

thanks
 
Upvote 0
it seems i get you better after critically reading all over again... you want the Last cell in Column B to have the sum of the cells above, if the sum is 900, the last cell should retain 900 otherwise it should leave it blank and the display a message that it cannot be summed. isn't it?
 
Upvote 0
Good! Do you want to run the code after you've filled your values or you want the code to execute while you input the values?
 
Upvote 0
yes this is what i want
If the messages will not be disturbing you use this

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastCell As Integer
Dim mySheet As Worksheet
Dim dSum As Long
Set mySheet = ActiveSheet
LastCell = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
dSum = WorksheetFunction.Sum(Range("B1:B" & LastCell))
If dSum = 900 Then
    mySheet.Cells(LastCell + 1, "B").Value = dSum
Else
    MsgBox "Sorry, it can't be summed"
End If
End Sub

and If you plan to run the code after the input of your data then use this
VBA Code:
Sub SuminLastCell()
Dim LastCell As Integer
Dim mySheet As Worksheet
Dim dSum As Long
Set mySheet = ActiveSheet
LastCell = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
dSum = WorksheetFunction.Sum(Range("B1:B" & LastCell))
If dSum = 900 Then
    mySheet.Cells(LastCell + 1, "B").Value = dSum
Else
    MsgBox "Sorry, it can't be summed"
End If
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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