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
- Excel 2007 conditional formatting
- Excel Macro help - conditional copy and paste from one worksheet to another
- Newbie VBA Macro question- Conditional Copy & Paste based on time
- sum of series of natural numbers
- sum and difference of 2 bcd numbers
- Compute the sum of both positive and negative elements of an array
- Getting the sum and percentage in Excel coulmn
- sum and Multiply number and print in decimal
- VBA conditional formatting - copy & paste
- VBA conditional formatting - copy & paste
- Create a constructor class Sum
- sum formula
- Conditional Formatting Or Select Case
- Specifying conditional formatting for the entire row
- how to sum the even numbers only
- Sum values entered into text boxes
- Data Sum rows not hidded
- Conditional Formatting in Access 2003
- Conditional format
- Formula with conditional text
- Calculate Total Sum
- problem with conditional output in templates
- Sum colunm total in repeater
- conditional redirect