 Search:

# Conditional Summing

Asked By: Sean    Date: Sep 24    Category: MS Office    Views: 2339

I have two columns A and B. Both columns A and B runs from A1 through
A4000 and B1 thro B4000 (in other words 4000 records each). I want to
have a new column C. The user can select whatever range of cells from A
and may click a button or something. The sum of those values in cell A
divided by a number (say 2000) should appear in the Column C in the
cell that is the last of the user selected range of cells. How do I
write a macro for this. Iam kinda new to macro language. All i know in
macros is basic stuff. If there is no need of macro in the first place,
let me know.

Share:

It would not really require a macro. If the user gives a cell then a sum could
be done using the offset or match function whatever might be the requirement.
eg the data is from A1 to b4000 and the user writes 650 meaning a1 to a650 then
a formula can be used on cell c2 as
=sum(a1:offset(a1,c1,0)

Happens when you are multitasking 3 projects!

Sub SumSelectedCells()
aRandomNumber = 1
LastInRange = Selection.Rows.Count
vSum = WorksheetFunction.Sum(Range(CellsToSum))
Range(CellsToSum)(LastInRange).Offset(0, 2).Value = vSum / aRandomNumber
End Sub

See if this fits the bill;

Sub SumSelectedCells()
aRandomNumber = 1
LastInRange = Selection.Rows.Count
vSum = WorksheetFunction.Sum(Range(CellsToSum))
Range(CellsToSum)(LastInRange).Offset(0, 2).Value = vSum / vRan
End Sub

You can replace the 'aRandomNumber' value with whatever you want!
You can add additional logic to check if the selection IS in columnA with:

vColumnSelected=range(CellsToSum).columns.column
If Column =1 then
SumSelectedCells
endif

last transmission may have truncated the logic....

should look like:

Sub SumSelectedCells()
aRandomNumber = 1
LastInRange = Selection.Rows.Count
vSum = WorksheetFunction.Sum(Range(CellsToSum))
Range(CellsToSum)(LastInRange).Offset(0, 2).Value = vSum / vRandomNumber
End Sub

If you really need to have the result on the last user-selected row,
then you will need to do the following:

Logic statement:
' User selects a range in column A, and then presses the "Do Column C
Now" button.
' Determine the top and bottom rows the user has selected.
' Put the formula in the correct row in Column C, using offset.

Actual Code:
Sub DoColumnCNow()

Dim Start_Row, Number_Of_Rows As Integer

' Determine the top and bottom rows the user has selected.
Start_Row = Selection.Row
Number_Of_Rows = Selection.Rows.Count

' Put the formula in the correct row in Column C, using offset.
Range("A" & Start_Row).Offset(Number_Of_Rows - 1, 2).Formula =
"=SUM(""A" & Start_Row & ":A" & Start_Row + Number_Of_Rows - 1 & "") /
2000

End Sub
------------------------------------------------------------------------
-----------
Now, my formula has a mistake in. See if you can fix the compile error,
and it will work. Or anyone else, please. Too late for my brain today.

I'm not sure why you've mentioned column B, as you never use it again. I
suspect that your explanation might be incorrect. However, I've taken it
exactly as you had it. I.e. use the selected range to position a formula in
C at the bottom of the selection and the sum is from column A. I'm also
assuming that the number you want to divide by is in F1.

Option Explicit

Private Sub CommandButton1_Click()
Dim TopRow As Integer
TopRow = Selection.Row
Dim Height As Integer
Height = Selection.Rows.Count
Dim BottomRow As Integer
BottomRow = TopRow + Height - 1
Range("C" & BottomRow).Formula = "=sum(A" & TopRow & ":A" & BottomRow & ")
/ \$F\$1"
End Sub

The only unusual thing I've done is to ignore part of the current selection.
I get the top row's number, plus the number of rows in the selection,
allowing me to determine the bottom row. I then force the summed range to
be in column A and the put the formula in column C. This means that you
don't actually have to select the correct column before hitting the button -
just make sure that the right rows are in the selection.

Column B is not needed. i shall try your code today, and let you know
thanks for the help.

Didn't find what you were looking for? Find more on Conditional Summing Or get search suggestion and latest updates.