Looping calculations for blocks of data

[Atvolpini]

Hi,

I have recorded a macro that is performing a couple of simple calculations on a dataset that is 12 columns wide by 8 rows tall. However, I now need to to have the macro loop those same calculations over each 12x8 block, until a blank row is reached. The data is very regular, every set is the same size and the next set occurs directly after. If desired Id be happy to upload the code I have. Here is an example of the data:

0.000	0.268	0.260	0.385	0.205	0.438	-0.001	0.381	0.222	0.732	0.495	-0.002
-0.001	0.327	0.357	0.334	0.748	0.243	-0.002	0.351	0.288	0.389	0.028	0.271	High Growth	15
0.783	0.000	0.767	0.000	0.227	0.368	0.381	0.765	0.153	0.425	0.423	0.306	Med Growth	51
0.303	0.237	0.350	0.000	0.466	0.498	0.099	0.320	0.001	0.774	0.780	0.307	Low Growth	8
0.065	0.738	0.340	-0.001	0.154	0.352	0.779	0.720	0.449	0.746	-0.002	0.023	No Growth	22
-0.002	0.191	0.346	0.087	0.787	0.257	0.317	0.001	-0.001	0.318	0.691	0.232	Sum	96
0.687	-0.002	0.544	0.052	0.344	0.752	0.113	0.691	0.470	-0.002	0.481	-0.002
0.000	0.340	0.252	0.411	0.293	0.238	0.371	0.267	0.712	0.705	-0.002	0.003
0.001	0.321	0.274	0.424	0.250	1.489	0.600	0.801	0.296	0.056	0.008	0.000
0.318	0.180	0.087	0.306	0.010	0.329	0.323	0.288	0.335	0.263	0.011	0.217
0.241	0.154	0.322	0.183	0.733	0.001	0.321	0.726	0.213	0.308	0.005	0.299
0.744	0.263	0.344	0.482	0.514	0.276	0.286	0.278	0.229	0.370	0.389	0.780
0.001	0.390	0.245	0.186	0.070	0.010	0.298	0.271	0.291	0.352	0.000	0.646
0.222	0.144	0.335	0.000	0.348	0.248	0.708	0.450	0.344	0.301	0.096	0.007
0.281	0.331	0.348	0.687	0.330	0.301	0.079	0.381	0.430	0.308	0.185	0.007
0.000	0.592	0.677	0.322	0.288	0.113	0.357	0.263	0.239	0.198	0.736	-0.001

<colgroup><col span="12"><col><col></colgroup><tbody>
</tbody>

 

[John_w]

Record your macro with relative references and start and finish on the same relative cell in the first 2 datasets. Then have another macro which loops through the datasets, calling the recorded macro on each dataset.

For example, I assumed your data starts in A1. So I selected A1, started the macro recorder with relative references, created formulas for the first dataset, (e.g. in M1 I entered the formula =SUM(A1:L1)), then selected the first cell of the next dataset (e.g. A10) and stopped the recorder. This produced Macro1 below and I wrote the All_DataSets macro to loop through all the datasets.

Sub All_DataSets()
    Dim lastRow As Long, r As Long
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").Select
    For r = 1 To lastRow Step 10
        Macro1
    Next
End Sub

Sub Macro1()
    ActiveCell.Offset(0, 12).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    ActiveCell.Offset(9, -12).Range("A1").Select
End Sub

 

[Atvolpini]

I tried to use the code you provided as a base, but it seems to only process using one formula, while I have multiple. I tried to add in more, but that causes the macro to only process the first data block. I have very little VBA knowledge so I might just be misunderstanding what you mean.

Sub Formulas()
    ActiveCell.Offset(0, 12).Range("A1").Select
        Range("Q2").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C[-14]:R[6]C[-3], "">=0.7"")"
        Range("Q3").Select
        ActiveCell.FormulaR1C1 = _
            "=COUNTIFS((R[-2]C[-14]:R[5]C[-3]),(""<0.7""),(R[-2]C[-14]:R[5]C[-3]),("">=0.2""))"
        Range("Q4").Select
        ActiveCell.FormulaR1C1 = _
            "=COUNTIFS((R[-3]C[-14]:R[4]C[-3]),(""<0.2""),(R[-3]C[-14]:R[4]C[-3]),("">=0.05""))"
        Range("Q5").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[-4]C[-14]:R[3]C[-3], ""<0.05"")"
        Range("Q6").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    ActiveCell.Offset(8, -14).Range("A1").Select
End Sub

 

[John_w]

Yes, my recorded macro only has one formula, just for demonstration purposes. Follow my steps carefully and record your macro with all your formulas, or just 2 or 3 to start with. Make sure the macro recorder is using relative references, not the default absolute references.

What are your formulas, exactly? It's hard to read the R1C1 formulas in your code.

Which cell does the data start in?

 

[Rick Rothstein]

I have recorded a macro that is performing a couple of simple calculations on a dataset that is 12 columns wide by 8 rows tall.

It is hard to give you a code approach without know what those "simple calculations" are... care to describe them for us?

 

[Fluff]

What is the last column with data? & does that column have values, or formulae?

 

[Atvolpini]

Thank you all for the help, i apologize for any confusion. Between John_w's recommendations and a little tampering I got the macro running successfully. Here is what ended up working. Just for completeness, Rick Rothstein, the calculations in their general form are also included below.

High Growth	=COUNTIF(C1:N8, ">0.7")
Med Growth	=COUNTIFS((C1:N8),("<0.7"),(C1:N8),(">0.2"))
Low Growth	=COUNTIFS((C1:N8),("<0.2"),(C1:N8),(">0.05"))
No Growth	=COUNTIF(C1:N8, "<0.05")
Sum	=SUM(B1:B4)

<tbody>
</tbody>

Sub Relative_OD_Loop()
' Keyboard Shortcut: Ctrl+Shift+I
    Application.ScreenUpdating = False
    Range("A1").Select
    Do Until IsEmpty(ActiveCell)
       Relative_OD
    Loop
End Sub
____________


Sub Relative_OD()
'
' Relative_OD Macro
'


    ActiveCell.Offset(1, 14).Range("A1").Select
    ActiveCell.FormulaR1C1 = "High Growth"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Med Growth"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Low Groth"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Low Growth"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "No Growth"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Sum"
    ActiveCell.Offset(-4, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C[-13]:R[6]C[-2],"">=0.7"")"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS((R[-2]C[-13]:R[5]C[-2]),(""<0.7""),(R[-2]C[-13]:R[5]C[-2]),("">=0.2""))"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS((R[-3]C[-13]:R[4]C[-2]),(""<0.2""),(R[-3]C[-13]:R[4]C[-2]),("">=0.05""))"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-4]C[-13]:R[3]C[-2],""<0.05"")"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    ActiveCell.Offset(3, -15).Range("A1").Select
End Sub