Macro question

  • Thread starter Thread starter jlmccabes
  • Start date Start date
J

jlmccabes

This is where I start
Act 10 12 8 30
Bud 9 12 9 30

Trying to get here
Act 10 12 8 30
% 33.3 40.0 26.7 100.0
Bud 9 12 9 30
% 30.0 40.0 30.0 100.0

Large worksheet so trying to do a macro I can manually run (CTL+q) going to
second line, insert row, calculate each number based on the total number.
Tried several times, got line to insert, calculated first value, then it
went haywire. Not sure if should try again using the R1C1 system or not.
Small favor - please do not make it look toooooo easy.
 
One way:
Asssuming that your data starts in row 2
Sub this()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(i, 1)
If i <> 2 Then .EntireRow.Insert shift:=xlDown
.Offset(1, 0).Value = "%"
.Offset(1, 1).FormulaR1C1 = "=R[-1]C/R[-1]C[3]*100"
.Offset(1, 2).FormulaR1C1 = "=R[-1]C/R[-1]C[2]*100"
.Offset(1, 3).FormulaR1C1 = "=R[-1]C/R[-1]C[1]*100"
.Offset(1, 4).FormulaR1C1 = "=sum(RC[-3]:RC[-1]"
End With
Next i
End Sub
 
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = "%"
.Cells(i + 1, "B").Value = .Cells(i, "B").Value / .Cells(i,
"E").Value * 100
.Cells(i + 1, "C").Value = .Cells(i, "C").Value / .Cells(i,
"E").Value * 100
.Cells(i + 1, "D").Value = .Cells(i, "D").Value / .Cells(i,
"E").Value * 100
.Cells(i + 1, "E").Value = 100
Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
try this
Sub percentoftotal()
mc = 1
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
'MsgBox Cells(i, mc)
For j = 1 To 4
Cells(i + 1, mc + j) = _
Format(Cells(i, mc + j) / Cells(i, mc + 4), "0.00%")
Next j
Rows(i).Insert
Next i
Rows(2).Delete
End Sub
 
Thank You one and ALL.. I did ask you to not make it tooo easy... Could
have thought about it longer and made me feel better... Just kidding...
Have to go to work - will run it tonight and extend it out for all 13
columns. I can at least do that part with the start I have now.. Thank You
again
This is where I start
Act 10 12 8 30
Bud 9 12 9 30

Trying to get here
Act 10 12 8 30
% 33.3 40.0 26.7 100.0
Bud 9 12 9 30
% 30.0 40.0 30.0 100.0

Large worksheet so trying to do a macro I can manually run (CTL+q) going to
second line, insert row, calculate each number based on the total number.
Tried several times, got line to insert, calculated first value, then it
went haywire. Not sure if should try again using the R1C1 system or not.
Small favor - please do not make it look toooooo easy.
 
Back
Top