Looping calculations for blocks of data

A

Atvolpini

New Member
Joined
Oct 26, 2017
Messages
15
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:

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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.
Code: 
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
 
Last edited:
Upvote 0
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.

Code: 
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
Atvolpini said:
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.
Click to expand...
It is hard to give you a code approach without know what those "simple calculations" are... care to describe them for us?
 
Upvote 0
What is the last column with data? & does that column have values, or formulae?
 
Upvote 0
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.

[TABLE="width: 405"]
<tbody>[TR]
[TD]High Growth[/TD]
[TD]=COUNTIF(C1:N8, ">0.7")[/TD]
[/TR]
[TR]
[TD]Med Growth[/TD]
[TD]=COUNTIFS((C1:N8),("<0.7"),(C1:N8),(">0.2"))[/TD]
[/TR]
[TR]
[TD]Low Growth[/TD]
[TD]=COUNTIFS((C1:N8),("<0.2"),(C1:N8),(">0.05"))[/TD]
[/TR]
[TR]
[TD]No Growth[/TD]
[TD]=COUNTIF(C1:N8, "<0.05")[/TD]
[/TR]
[TR]
[TD]Sum[/TD]
[TD]=SUM(B1:B4)[/TD]
[/TR]
</tbody>[/TABLE]

Code:
Code: 
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

 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
367
Fluff
Fluff
D
  • Question Question
VBA Help with Array of text
2 3
Replies
21
Views
2K
decadence
D
B
  • Question Question
Help with Match/Index formula
Replies
3
Views
410
Brustigian
B
B
  • Question Question
Lookup Formula
Replies
1
Views
448
gaz_chops
G

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top