Make my macro dynamic

[fotis462]

Hi guys

I have a sheet that contains 3 columns (A,B,C). I have written the following macro and it works fine (even though I know that the syntax could be better). But I d like to make it dynamic, i.e. get rid of the cells' names ("F5:F23") and instead use something (maybe ActiveCell??) in order to be more generic. Hope that makes sense. Please have a look at the code and let me know how could I improve it and maybe what is the general logic in making a code dynamic.

Many thanks

Sub Sec3_Step2()
Range("A1").Select
Selection.End(xlToRight).Select
Selection.Offset(2, 3).Select
Selection.FormulaR1C1 = "Interval"
Selection.Offset(0, 1).Select
Selection.FormulaR1C1 = "Mean"
Selection.Offset(1, -1).Select

ActiveCell.FormulaR1C1 = "1"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("F5").Select
Selection.AutoFill Destination:=Range("F5:F23"), Type:=xlFillDefault

Selection.Offset(-1, 1).Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.NumberFormat = "0.000%"

ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],RC[-1],C[-4])/10000"
Selection.AutoFill Destination:=Range("G4:G23")


End Sub

 

[jbeaucaire]

Maybe like this? this will look at column A to see how far down the data goes, then it will do the work in columns F:G based on that many rows.

Option Explicit

Sub Sec3_Step2()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row   ' last row with data looking at col A

Range("F3") = "Interval"
Range("G3") = "Mean"

Range("F4") = 1
Range("F5:F" & LR).FormulaR1C1 = "=R[-1]C+1"
Range("G4:G" & LR).Style = "Percent"
Range("G4:G" & LR).NumberFormat = "0.000%"
Range("G4:G" & LR).FormulaR1C1 = "=SUMIF(C[-5],RC[-1],C[-4])/10000"

End Sub

 

[fotis462]

Hi jb and thanks for your reply

This will not work for 2 reasons.

a. The rows in the first 3 columns are 200000 where the rows in column F is only 20. (I am using a SUMIF function to aggregate some results and then take the average...)

b. My main problem is that I don't want to have the cells namely displayed, i.e. I don't want to have "F4" or "G4:G" or anything like that, because then I will be working on a spreasheet that has, say, 9 initial columns, and the column F will now be L. So instead of F or L I want to use something that it is independent of the initial number of columns.

Any ideas fully appreciated!

Thanks
fotis462

 

[jbeaucaire]

It's up to you to describe the logic a dynamic macro would have to employ.

1) I need to find the last column with data in it by looking across row 1 (will this work?)
2) I need to add two new columns outside that data, one for "Interval" and one for "Mean"

3) I need to put a "1" as the first interval in row2
4) I need to increment that index down a certain number of rows... the number of rows is determined by looking at ___________???

5) I need to determine which column to analyze for "Mean" by looking for the column that has_____________????????

6) I need to insert a formula in the mean column that analyzes the correct column and goes down the same number of rows as step #4
3) I need to determine how many rows of data