How to divide by summation of any column

**pban92** · 01-23-2012, 03:48 PM

(Wasted 7 hours and still counting with no luck so far...)

I have two columns A and B as follows,

21.405 20
21.561 31
21.719 158
21.877 484
22.036 1251
22.196 3060
22.357 7613
22.519 16065
22.681 33223
22.844 56712
23.009 96576
23.174 135266
23.339 171886
23.506 172951
23.674 155269
23.842 104310
24.012 59248
24.182 23244
24.353 8699
24.525 2018
24.698 418
24.871 64
25.046 5
26.046

I have to now create three more columns C, D and E, where,

C1 = A2-A1
D1 = B1/1048571 (The summation of column B is 1048571).
E1 = C1*D1

The following scripts do the job,

Sub PdfData()


    Range("C1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("D1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
     ActiveCell.FormulaR1C1 = "=RC[-2]/1048571/RC[-1]"
        ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("E1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop

End Sub

The problem is instead of putting 1048571 (the summation value of Column B) manually, how can I modify my scripts so that for any unknown number of rows, the summation would be automatically calculated (dividing by "sum(B1:B65000)" did not work) followed by the division operation.

Any suggestion would be highly appreciated.

Thanks!

**TMShucks** · 01-23-2012, 04:01 PM

Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C" & lLR).FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
Range("D1:D" & lLR).FormulaR1C1 = "=RC[-2]/SUM(C2)"
Range("E1:E" & lLR).FormulaR1C1 = "=RC[-2]*RC[-1]"

Regards, TMS

**tom1977** · 01-23-2012, 04:06 PM

try this

Sub PdfData()


    Range("C1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("D1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
     ActiveCell.FormulaR1C1 = "=RC[-2]/SUM(c[-2])/RC[-1]"
        ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("E1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop

End Sub

**pban92** · 01-23-2012, 04:15 PM

Hi, many thanks for your kind help!

1. But if you notice you would see that my Columns A and B do not have equal numbers of row. But new columns C, D and E needs to have equal numbers of row as Column B.

2. Summation of Column E values should be 1.

Could you pls tell me how I can at the same time satisfy the two above points?

Kind regards!