How can I write a code to define a range insdide a loop that will change its size?
<p>I need to use two Loops and the easy part is to count how many times does a "submodul" repeats in a defined and known range ("B3","B18"), this means the quantity of elements each submodul has. The difficult part comes when trying to count how many times does a "position" repeats for each different "submodul", this is because the amount of elements of each "submodul" is different so I have to adjust a range in a especial Loop to calculate how many times does a specific element (=Position) repeats within a "submodul".</p>

<p>The specific part that I need help with is the following:</p>

<pre><code>positionrepetition = Application.CountIf(Worksheets("Sheet2").range("cells(3 + x + y - 1, 3)", "cells(3 + x + y - 1 + submodulrepetition,3"), position)
</code></pre>

<p>If I can manage to write it in a correct format I believe it will work. The problem is that normally I only use the range function when I know that the range is fixed or known, it doesn´t have to be calculated. I normally write for example: Range("A1","F10").Select </p>

<p>As you can see this is a fixed range, so I imagined that instead of using the format of Range("A1", "F10") I could  use the range function with the arguments ("Cells(1,1)","Cells(10,6)"). Please correct me if I´m wrong.</p>

<p>Here is the rest of the code for the Loop.</p>

<pre><code>For x = 0 To numberofparts
    If Cells(3 + x, 18) = "1" Then
        submodul = Cells(3 + x, 2).Value
        submodulrepetition = Application.CountIf(Worksheets("Sheet2").range("B3", "B18"), submodul)
        For y = 1 To submodulrepetition
            position = Cells(3 + x + y - 1, 3).Value
            positionrepetition = Application.CountIf(Worksheets("Sheet2").range("cells(3 + x + y - 1, 3)", "cells(3 + x + y - 1 + submodulrepetition,3"), position)
        Next
    Else
    End If
    x = x + submodulrepetition - 1
 Next
</code></pre>

<p>To explain a little more, all data is gathered from Excel Sheets:</p>

<p>-All Information is gathered from a Excel sheet
-The "submodules" are in column B and they are arranged in numerical order. Every submodul repeats in this column as many elements it has.
-The "positions" (elements of the submodules) are in column C and can also repeat in the same column and even in other "Submodul"s.</p>

<p>All help will be appreciated and I thank you in advance.</p>

<p>Alejandro Farina</p>