semi automatic macro help

[Legacy 468939]

Hello there I am attempting to write a semi-automatic macro but I am struggling to get it to repeat all the functions? it appears only to want to repeat the first command and does not continue to follow the rest ? any help would be great appreciated

 

[Joe4]

OK, try this:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w

    Dim r As Long
        
    For r = 4 To 52 Step 12
        Cells(r, "D").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
        Cells(r, "E").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
        Cells(r, "I").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
        Cells(r, "J").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
    Next r
    
End Sub

 

[Legacy 468939]

OK, try this:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w

    Dim r As Long
       
    For r = 4 To 52 Step 12
        Cells(r, "D").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
        Cells(r, "E").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
        Cells(r, "I").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
        Cells(r, "J").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
    Next r
   
End Sub

that did work thank you, how do I make it a semi-automatic macro ? so that every time I press ctrl+w it goes down 12 rows ? I know its less convenient but my brief states a semi-automatic macro.

 

[Joe4]

I know its less convenient but my brief states a semi-automatic macro.

So is this some sort of homework assignment?

Actually, you already pieces you need in this thread to do that. Take a look at the earlier posts I made in this thread where I showed you how to find the last populated row in columns D. Simply use that and add 12 to move down 12 rows.

Rather than do this all for you, I am going to let you put those pieces together. If you run into trouble, post what you tried and I will help you clean it up.

 

[Legacy 468939]

So is this some sort of homework assignment?

Actually, you already pieces you need in this thread to do that. Take a look at the earlier posts I made in this thread where I showed you how to find the last populated row in columns D. Simply use that and add 12 to move down 12 rows.

Rather than do this all for you, I am going to let you put those pieces together. If you run into trouble, post what you tried and I will help you clean it up.

not homework its practice exercises for an excel course but I'm struggling its not assessed but its supposed to get us looking at/thinking about macro functionality.
Application.CutCopyMode = False
Range("D4").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Application.CutCopyMode = False
Range("E4").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
Application.CutCopyMode = False
Range("I4").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Application.CutCopyMode = False
Range("J4").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
ActiveCell.Offset(9, 0).Range("A4").Select

this is where I am it gets me to my next cell D16 but its not repeating the calculations for cells E16, I16 and J16 ?


End Sub

 

[Joe4]

Why did you go back your old way? You cannot use that code, as that is hard-coded all the cells, and you don't want it to do that.

You need to borrow from the last code post I made, and the "last row" functionality I showed you earlier.
You need to get used to using variables if you want to make dynamic code.

So you would want your code to look something like this:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w

    Dim r As Long
    Dim lr As Long
    
'   Find last row with entry in column D
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    
'   If last row in column is less than 4, set r to 4, else add 12
    If lr < 4 Then
        r = 4
    Else
        r = lr + 12
    End If
        
'   Enter formulas on row r
    Cells(r, "D").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
    Cells(r, "E").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
    Cells(r, "I").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
    Cells(r, "J").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
    
End Sub

Note that I added comments to the code to tell you what is going on. Take the time to read through it, to see if it makes sense.

 

[Legacy 468939]

Why did you go back your old way? You cannot use that code, as that is hard-coded all the cells, and you don't want it to do that.

You need to borrow from the last code post I made, and the "last row" functionality I showed you earlier.
You need to get used to using variables if you want to make dynamic code.

So you would want your code to look something like this:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w

    Dim r As Long
    Dim lr As Long
   
'   Find last row with entry in column D
    lr = Cells(Rows.Count, "D").End(xlUp).Row
   
'   If last row in column is less than 4, set r to 4, else add 12
    If lr < 4 Then
        r = 4
    Else
        r = lr + 12
    End If
       
'   Enter formulas on row r
    Cells(r, "D").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
    Cells(r, "E").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
    Cells(r, "I").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
    Cells(r, "J").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
   
End Sub

Note that I added comments to the code to tell you what is going on. Take the time to read through it, to see if it makes sense.

ok many thanks for all the help

 

[Joe4]

You are welcome.