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]

Welcome to the Board!

What do you mean by "repeat"? Are you saying that you want to copy these formulas from row 4 all the way down to row 16?

If so, simply modify the range for each function (and you can combine each ".Select" line with the "ActiveCell" formula below it, i.e.

Range("E4:E16").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"

and repeat this structure for each of your other formulas.

 

[Legacy 468939]

Welcome to the Board!

What do you mean by "repeat"? Are you saying that you want to copy these formulas from row 4 all the way down to row 16?

If so, simply modify the range for each function (and you can combine each ".Select" line with the "ActiveCell" formula below it, i.e.

Range("E4:E16").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"

and repeat this structure for each of your other formulas.

hello,

apologies if I was unclear, I want the macro to re-run that sequence but at D16. As Im calculating daily averages so I want to run a macro that will calculate the avg/max for a day then jump to the next day and calculate those and so on. I began my macro on D4 so it calculates the average max for 2 days but when it ends at D16 it only calculates the average in D16 but not for E16, I 16 J1. I just want the macro to repeat the sequence again, then after it would go to D26 to work out the values for D26,E26,I26,J26.

 

[Legacy 468939]

apologies if there is confusion. I want the macro to repeat the calculations but at d16 but for some reason, it only calculates the average at D16 but not for E16, I16 OR J16

 

[Joe4]

If I understand your correctly, then I think all you need to do is copy the formulas form columns D,E,I, and J from the last row to the next available row, right?

This code would do that:

Sub MyFormulaCopy()

    Dim lr As Long
    
'   Find last row with entry in column D
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy formulas to columns D,E to next row
    Range("D" & lr & ":E" & lr).Copy Range("D" & lr + 1)
    
'   Copy formulas to columns I,J to next row
    Range("I" & lr & ":J" & lr).Copy Range("I" & lr + 1)
    
End Sub

 

[Legacy 468939]

If I understand your correctly, then I think all you need to do is copy the formulas form columns D,E,I, and J from the last row to the next available row, right?

This code would do that:

Sub MyFormulaCopy()

    Dim lr As Long
   
'   Find last row with entry in column D
    lr = Cells(Rows.Count, "D").End(xlUp).Row
   
'   Copy formulas to columns D,E to next row
    Range("D" & lr & ":E" & lr).Copy Range("D" & lr + 1)
   
'   Copy formulas to columns I,J to next row
    Range("I" & lr & ":J" & lr).Copy Range("I" & lr + 1)
   
End Sub

Basically im looking at average and max temperatures for two sites. I calculated avg in D4, max in E4, avg in I4 max in J4 then moved my cursor to D16 and stopped the macro. When I run it it makes the calculations for D4,E4I4,J4 and ends on D16. When I run the macro again it only calculate the average in D16 then jumps back to E4,I4,J4 and not to E14,I14,J14. This is what the code looks like ?

 

[Joe4]

That is because you do are not selecting a range before your first formula.
It is starting in the ActiveCell (whatever cell you are in when you start the macro).

Note that while the Macro Recorder is a great tool for getting snippets of code, there are a few things you need to understand about it, such as It is very literal. It records every screen scrolling and cell selection. These are often unnecessary to the code, and the code can actually be sped up by removing them. You do not need to actually select a cell to put a formula in it.

In your case, every coupling where you have something like:

Range("E4").Select
ActiveCell.FormulaR1C1 = ...

it can be simplified to:

Range("E4").FormulaR1C1 = ...

This will make your code shorter, faster, and ensure that it happens on the correct range.

So all you need in the body of your code is your four formulas:

Range("D4").FormulaR1C1 = ...
Range("E4").FormulaR1C1 = ...
Range("I4").FormulaR1C1 = ...
Range("J4").FormulaR1C1 = ...

I will leave it to you copy in your formulas.

Note that in the future, instead of posting a picture of your code, please copy and paste your VBA code directly in here. Then we can copy and edit it for you.
There are even "VBA" code tags up in the Editor which will keep your code formatting nicely, like I have done above.

 

[Legacy 468939]

That is because you do are not selecting a range before your first formula.
It is starting in the ActiveCell (whatever cell you are in when you start the macro).

Note that while the Macro Recorder is a great tool for getting snippets of code, there are a few things you need to understand about it, such as It is very literal. It records every screen scrolling and cell selection. These are often unnecessary to the code, and the code can actually be sped up by removing them. You do not need to actually select a cell to put a formula in it.

In your case, every coupling where you have something like:

Range("E4").Select
ActiveCell.FormulaR1C1 = ...

it can be simplified to:

Range("E4").FormulaR1C1 = ...

This will make your code shorter, faster, and ensure that it happens on the correct range.

So all you need in the body of your code is your four formulas:

Range("D4").FormulaR1C1 = ...
Range("E4").FormulaR1C1 = ...
Range("I4").FormulaR1C1 = ...
Range("J4").FormulaR1C1 = ...

I will leave it to you copy in your formulas.

Note that in the future, instead of posting a picture of your code, please copy and paste your VBA code directly in here. Then we can copy and edit it for you.
There are even "VBA" code tags up in the Editor which will keep your code formatting nicely, like I have done above.

ok thank you very much

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

Range("D4").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Range("E4").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
Range("I4").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Range("J4").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
End Sub

that is my code at the minute how do i get it to move down to D16 and repeat the calculations but for E,I,J14 and then go to D28 and repeat for EI,J28 and then D30 etc ? I

 

[Joe4]

how do i get it to move down to D16 and repeat the calculations but for E,I,J14

This is a bit confusing. I am not clear if you want the formula in cell D16 to look at the values in row 4 or row 16. So let's try to clarify it this way.

Instead of VBA, go out to your sheet, and copy/paste the formula shown in cell D4 here.
Then, using this same methodology, show us the formula that you want to appear in D16.

Also, what is the "jump" here in rows? At first, it appears that you are jumping by 12 (4 to 16 to 28), but then you go to 30, which blows that assumption out of the water.
So, how exactly do we determine which rows you want to apply this to?

 

[Legacy 468939]

This is a bit confusing. I am not clear if you want the formula in cell D16 to look at the values in row 4 or row 16. So let's try to clarify it this way.

Instead of VBA, go out to your sheet, and copy/paste the formula shown in cell D4 here.
Then, using this same methodology, show us the formula that you want to appear in D16.

Also, what is the "jump" here in rows? At first, it appears that you are jumping by 12 (4 to 16 to 28), but then you go to 30, which blows that assumption out of the water.
So, how exactly do we determine which rows you want to apply this to?

D4 = =AVERAGE(C4:C15)
D16 ==AVERAGE(C16:C27)
Apologies D30 was an error on my part i meant D40.
I want to calculate average for D4, max for E4. Average for I4, max for J14.
Then automatically move down to D16 as im calculating data over 12 values.
Then calculate average for D16, max for E16. Average for I16, max for J14.
Then go to D40 to calculate average for D40, max for E40. Average for I40, max for J40.
so essentially loop the macro so it moves down to D16,D28,D40,D52 and calculates the values for all the cells in the column.