VBA excel

Question

gerchi152 0 Light Poster

12 Years Ago

My process is to multiply the given points in B8, D8, F8, H8, J8, L8, and N8 to A3, B3, C3, D3, E3, F3, G3 respectively. this procedure is to be processed in C8, E8, G8, I8, K8, M8, and O8. after multiplying, the process should perform =sum(C8,E8,G8,I8,K8,M8,O8) to P8. I have this code which is according to recorded macro.

Sub compute()
'
' compute Macro
'

'
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-2]"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-3]"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-4]"
    Range("I8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-5]"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-6]"
    Range("M8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-7]"
    Range("O8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*R[-5]C[-8]"
    Range("P8").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM(RC[-1],RC[-3],RC[-5],RC[-7],RC[-9],RC[-11],RC[-13],)"
    Range("Q8").Select
    ActiveWindow.SmallScroll ToRight:=4
End Sub

how can I repeat the said procedures above to the next row by just clicking a button containing the code of recorded macro?

copy excel insert next row vba visual-basic visual-basic-6

Edited 12 Years Ago by TrustyTony because: fencing ~~~ vb code tags added

2 Contributors
1 Reply
378 Views
3 Weeks Discussion Span
Latest Post 11 Years Ago Latest Post by davesexcel

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

davesexcel -2 Newbie Poster · Answer 1 · 2012-04-21T17:44:56+00:00

If you have a cell selected in the row you want copied, then you can use the code below to insert the copied rows to the next row.

Sub Button2_Click()

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 16)).Copy
Cells(ActiveCell.Row + 1, 1).Insert Shift:=xlDown
Application.CutCopyMode = 0


End Sub

'Here is you original code cleaned up a bit.

Sub compute()

    Range("C8") = "=RC[-1]*R[-5]C[-2]"
    Range("E8") = "=RC[-1]*R[-5]C[-3]"
    Range("G8") = "=RC[-1]*R[-5]C[-4]"
    Range("I8") = "=RC[-1]*R[-5]C[-5]"
    Range("K8") = "=RC[-1]*R[-5]C[-6]"
    Range("M8") = "=RC[-1]*R[-5]C[-7]"
    Range("O8") = "=RC[-1]*R[-5]C[-8]"
    Range("P8") = "=SUM(RC[-1],RC[-3],RC[-5],RC[-7],RC[-9],RC[-11],RC[-13],)"

End Sub

SPAM DELETED