Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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

CellsToSum = Selection.Address

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

CellsToSum = Selection.Address

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

CellsToSum = Selection.Address

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.

Related Topics:

- sum cells with conditional formatting color
- VBA code to sum
- Excel 2000 Conditional formatting
- chart conditional formatting
- Conditional delete from List Box
- Loop, sum values and paste
- Conditional formatting?
- Conditional Formatting.
- Summing a range of variable size
- Macro help newbie: VB conditional if statement to copy paste cell
- custom Sum function?
- SUM content of rows
- count the sum of a column
- Conditional deletes and page updating
- sum of amount deliverd group
- conditional redirect
- Sum colunm total in repeater
- problem with conditional output in templates
- Calculate Total Sum
- Conditional Formatting
- Conditional hyperlink
- getting the sum of the last 20 cells
- Excel VBA - need to copy paste conditional statement
- Summing a range
- Summing on varying number of rows in Excel using VBA