looping into array show only the first block of 5 items

[sal21]

Code: Select all

Why the array show only the first block of 5 items?

Option Explicit
Sub PRINT_BLOCCO()

Dim Y As Long, X As Long, RIGA As Long, WS As Worksheet
Dim strDBRows() As Variant

Set WS = Sheets("DATA_BASE")

RIGA = 3

Erase strDBRows()
For Y = 3 To 11
strDBRows = Array(WS.Range("B" & RIGA).Value, Trim$(WS.Range("E" & RIGA).Value), Trim$(WS.Range("F" & RIGA).Value), Right$(Trim$(WS.Range("G" & RIGA).Value), 11), Trim$(WS.Range("D" & RIGA).Value))
RIGA = RIGA + 1
Next Y

For X = LBound(strDBRows) To UBound(strDBRows)
Debug.Print strDBRows(X)
Next

Erase strDBRows()

End Sub

[HansV]

Your code overwrites strDBRows each time in the loop. What exactly do you want to accomplish?

[sal21]

Your code overwrites strDBRows each time in the loop. What exactly do you want to accomplish?

In effect i need to store in array (and looping) the first block of 10 rows based the column into array...

[HansV]

Do you want a one-dimensional array of 50 items, or a two-dimensional array of 10 x 5 items?

[sal21]

Do you want a one-dimensional array of 50 items, or a two-dimensional array of 10 x 5 items?

Good mornung Hans!
Happy to have understand me, now.

I need:
"...two-dimensional array of 10 x 5 items"

Tks.

[HansV]

In fact, it's 9 x 5 because 3 to 11 is 9 rows. Here is the modified code:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim WS As Worksheet
    Dim arrDBRows(1 To 9, 1 To 5) As Variant

    Set WS = Sheets("DATA_BASE")
    For X = 3 To 11
        arrDBRows(X - 2, 1) = WS.Range("B" & X).Value
        arrDBRows(X - 2, 2) = Trim(WS.Range("E" & X).Value)
        arrDBRows(X - 2, 3) = Trim(WS.Range("F" & X).Value)
        arrDBRows(X - 2, 4) = Right(Trim(WS.Range("G" & X).Value), 11)
        arrDBRows(X - 2, 5) = Trim(WS.Range("D" & X).Value)
    Next X

    For X = 1 To 9
        For Y = 1 To 5
            Debug.Print arrDBRows(X, Y)
        Next Y
    Next X
End Sub

[sal21]

In fact, it's 9 x 5 because 3 to 11 is 9 rows. Here is the modified code:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim WS As Worksheet
    Dim arrDBRows(1 To 9, 1 To 5) As Variant

    Set WS = Sheets("DATA_BASE")
    For X = 3 To 11
        arrDBRows(X - 2, 1) = WS.Range("B" & X).Value
        arrDBRows(X - 2, 2) = Trim(WS.Range("E" & X).Value)
        arrDBRows(X - 2, 3) = Trim(WS.Range("F" & X).Value)
        arrDBRows(X - 2, 4) = Right(Trim(WS.Range("G" & X).Value), 11)
        arrDBRows(X - 2, 5) = Trim(WS.Range("D" & X).Value)
    Next X

    For X = 1 To 9
        For Y = 1 To 5
            Debug.Print arrDBRows(X, Y)
        Next Y
    Next X
End Sub

Hans, no dubt, the code work perfect!

But i have forgot the most important thing...

The sheet contain 417 rows (started from the 3° rows)
I need to fill the array for a block of 10 rows until the last row, in this case is the row 417.

In effect i need to fill the first block of 10 rows and call mymacro, go to next block from row 11 to 20, call mymacro, go to next block from row 21 to 30, call mymacro, ecc... stop the filing of array when row is 417.
In my case the last block not is 10 but 400 to 417.

I think you have understand me, as usual.

[HansV]

If we start at row 3 and work with blocks of 10 row, the first block is from row 3 to row 12, and the second block from row 13 to row 22, not from row 11 to 20.
Please try to explain very precisely what you want.

[sal21]

If we start at row 3 and work with blocks of 10 row, the first block is from row 3 to row 12, and the second block from row 13 to row 22, not from row 11 to 20.
Please try to explain very precisely what you want.

Yes you are rtight! i'm wrong to count the row... but my idea is exacltlly 3 to 12, 13 to 22, eccc....
Sorry for that

[HansV]

You'll have to pass the array to the "macro" that you call:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    Dim WS As Worksheet

    Set WS = Sheets("DATA_BASE")
    ' Number of rows per block
    Z = 10
    For X = 3 To 403 Step 10
        ' More rows in the last block
        If X = 403 Then
            Z = 15
        End If
        ' ReDim the array clears it
        ReDim arrDBRows(1 To Z, 1 To 5) As Variant
        ' Populate array
        For Y = 1 To Z
            arrDBRows(Y, 1) = WS.Range("B" & (X + Y - 1)).Value
            arrDBRows(Y, 2) = Trim(WS.Range("E" & (X + Y - 1)).Value)
            arrDBRows(Y, 3) = Trim(WS.Range("F" & (X + Y - 1)).Value)
            arrDBRows(Y, 4) = Right(Trim(WS.Range("G" & (X + Y - 1)).Value), 11)
            arrDBRows(Y, 5) = Trim(WS.Range("D" & (X + Y - 1)).Value)
        Next Y
        ' Do something with the array
        Call MyMacro(arrDBRows)
    Next X
End Sub

Sub MyMacro(MyArray As Variant)
    Dim X As Long
    Dim Y As Long
    For X = LBound(MyArray, 1) To UBound(MyArray, 1)
        For Y = LBound(MyArray, 2) To UBound(MyArray, 2)
            ' Do something with MyArray, for example
            Debug.Print MyArray(X, Y)
        Next Y
    Next X
End Sub

[sal21]

You'll have to pass the array to the "macro" that you call:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    Dim WS As Worksheet

    Set WS = Sheets("DATA_BASE")
    ' Number of rows per block
    Z = 10
    For X = 3 To 403 Step 10
        ' More rows in the last block
        If X = 403 Then
            Z = 15
        End If
        ' ReDim the array clears it
        ReDim arrDBRows(1 To Z, 1 To 5) As Variant
        ' Populate array
        For Y = 1 To Z
            arrDBRows(Y, 1) = WS.Range("B" & (X + Y - 1)).Value
            arrDBRows(Y, 2) = Trim(WS.Range("E" & (X + Y - 1)).Value)
            arrDBRows(Y, 3) = Trim(WS.Range("F" & (X + Y - 1)).Value)
            arrDBRows(Y, 4) = Right(Trim(WS.Range("G" & (X + Y - 1)).Value), 11)
            arrDBRows(Y, 5) = Trim(WS.Range("D" & (X + Y - 1)).Value)
        Next Y
        ' Do something with the array
        Call MyMacro(arrDBRows)
    Next X
End Sub

Sub MyMacro(MyArray As Variant)
    Dim X As Long
    Dim Y As Long
    For X = LBound(MyArray, 1) To UBound(MyArray, 1)
        For Y = LBound(MyArray, 2) To UBound(MyArray, 2)
            ' Do something with MyArray, for example
            Debug.Print MyArray(X, Y)
        Next Y
    Next X
End Sub

wow!!!!


But if the and of rows are variable and not fixed?

I have see in your cod you chec the end of row with:
If X = 403 Then
Z = 15
End If

[HansV]

How do you want to handle the last rows if the total number of rows (starting at row 3) is not a multiple of 10? Earlier, you wrote "In my case the last block not is 10 but 400 to 417". How do we do that if the number of rows is variable?

[sal21]

How do you want to handle the last rows if the total number of rows (starting at row 3) is not a multiple of 10? Earlier, you wrote "In my case the last block not is 10 but 400 to 417". How do we do that if the number of rows is variable?

ULTIMA = WS.Range("A65536").End(xlUp).Row...(?)

[HansV]

Yes, I know that. What I meant is: if the number of rows is not divisible by 10, what should the last block be?

For example if there are 629 rows, do you want 61 blocks of 10 rows and 1 block of 19 rows, or 62 blocks of 10 rows and 1 block of 9 rows?
And if there are 621 rows, do you want 61 blocks of 10 rows and 1 block of 11 rows, or 62 blocks of 10 rows and 1 block of 1 row?

[sal21]

Yes, I know that. What I meant is: if the number of rows is not divisible by 10, what should the last block be?

For example if there are 629 rows, do you want 61 blocks of 10 rows and 1 block of 19 rows, or 62 blocks of 10 rows and 1 block of 9 rows?
And if there are 621 rows, do you want 61 blocks of 10 rows and 1 block of 11 rows, or 62 blocks of 10 rows and 1 block of 1 row?

For example if there are 629 rows, do you want 61 blocks of 10 rows and 1 block of 19 rows, or 62 blocks of 10 rows and 1 block of 9 rows?

or 62 blocks of 10 rows and 1 block of 9 rows


And if there are 621 rows, do you want 61 blocks of 10 rows and 1 block of 11 rows, or 62 blocks of 10 rows and 1 block of 1 row?

or 62 blocks of 10 rows and 1 block of 1 row

TKS for your patience

[HansV]

Here is the new version:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    Dim M As Long
    Dim WS As Worksheet

    Set WS = Sheets("DATA_BASE")
    M = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
    ' Number of rows per block
    Z = 10
    For X = 3 To M Step 10
        ' More rows in the last block
        If X > M - 9 Then
            Z = M - X + 1
        End If
        ' ReDim the array clears it
        ReDim arrDBRows(1 To Z, 1 To 5) As Variant
        ' Populate array
        For Y = 1 To Z
            arrDBRows(Y, 1) = WS.Range("B" & (X + Y - 1)).Value
            arrDBRows(Y, 2) = Trim(WS.Range("E" & (X + Y - 1)).Value)
            arrDBRows(Y, 3) = Trim(WS.Range("F" & (X + Y - 1)).Value)
            arrDBRows(Y, 4) = Right(Trim(WS.Range("G" & (X + Y - 1)).Value), 11)
            arrDBRows(Y, 5) = Trim(WS.Range("D" & (X + Y - 1)).Value)
        Next Y
        ' Do something with the array
        Call MyMacro(arrDBRows)
    Next X
End Sub

[sal21]

Here is the new version:

Code: Select all

Sub PRINT_BLOCCO()
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    Dim M As Long
    Dim WS As Worksheet

    Set WS = Sheets("DATA_BASE")
    M = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
    ' Number of rows per block
    Z = 10
    For X = 3 To M Step 10
        ' More rows in the last block
        If X > M - 9 Then
            Z = M - X + 1
        End If
        ' ReDim the array clears it
        ReDim arrDBRows(1 To Z, 1 To 5) As Variant
        ' Populate array
        For Y = 1 To Z
            arrDBRows(Y, 1) = WS.Range("B" & (X + Y - 1)).Value
            arrDBRows(Y, 2) = Trim(WS.Range("E" & (X + Y - 1)).Value)
            arrDBRows(Y, 3) = Trim(WS.Range("F" & (X + Y - 1)).Value)
            arrDBRows(Y, 4) = Right(Trim(WS.Range("G" & (X + Y - 1)).Value), 11)
            arrDBRows(Y, 5) = Trim(WS.Range("D" & (X + Y - 1)).Value)
        Next Y
        ' Do something with the array
        Call MyMacro(arrDBRows)
    Next X
End Sub

great!!!!!!!!!!!!!!!!

Note:
In other case i dont love much the beer i love, sure, a good glass of wine!
I live in Italy, remember...

[HansV]

A glass of Chianti Classico will do nicely, thank you!