Variant Array - Last Row and Placement

[GeeWhiz7]

Hi folks,
Yet another thing I seem to be stuck on. Code and mini-sheet below to illustrate what I'm trying to do.

Issue:
I have an input array that is not always a fixed size. I would like to do some match on the items in that array and write out the result below the previous array with a space between.
This works fine if I calculate the last row and use it +2 to position the first output array by itself. But, I'd like to keep doing this with multiple arrays and then my approach starts to mess up and I can't quite spot why.

Goal:
Be able to do as many new arrays as I want, each stacked neatly below the previous with a space or two while still allowing for dynamic sizes of the input array.

Example Desired Result just using formulas in sheet

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	B	C	D	E
2	10.7	16.0	7.2	19.3	11.3			Original Inputs
3	4.3	14.1	5.0	15.8	19.1			These data array can change size by column and row
4	3.1	9.6	6.7	2.6	4.8
5	8.7	10.9	15.7	6.6	16.1
6	14.6	6.0	7.1	3.5	10.3
7	14.9	12.2	3.9	7.3	9.9
8	11.9	15.8	12.2	2.3	19.4
9	7.9	8.7	11.6	19.2	7.7
10	10.2	7.8	7.6	12.7	15.6
11
12	A	B	C	D	E			1st Calculation
13	21.3	32.0	14.4	38.7	22.7			Would like to have this calculation with one row space from original
14	8.6	28.1	10.0	31.7	38.1			=Original Array X 2
15	6.2	19.2	13.4	5.2	9.5
16	17.3	21.8	31.5	13.3	32.2
17	29.2	12.0	14.1	7.1	20.6
18	29.7	24.3	7.8	14.7	19.7
19	23.9	31.6	24.3	4.6	38.7
20	15.8	17.4	23.1	38.4	15.5
21	20.3	15.7	15.1	25.3	31.1
22
23	A	B	C	D	E			2nd Calculation
24	32.0	48.0	21.6	58.0	34.0			Same with each subsequent calculation would like one row space
25	12.9	42.2	15.0	47.5	57.2			=Original Array X 3
26	9.2	28.9	20.1	7.8	14.3
27	26.0	32.7	47.2	19.9	48.4
28	43.8	18.1	21.2	10.6	30.9
29	44.6	36.5	11.6	22.0	29.6
30	35.8	47.4	36.5	6.8	58.1
31	23.7	26.2	34.7	57.7	23.2
32	30.5	23.5	22.7	38.0	46.7
33
34	A	B	C	D	E			2nd Calculation
35	42.7	64.0	28.8	77.3	45.3			Same with each subsequent calculation would like one row space
36	17.2	56.2	20.0	63.3	76.3			=Original Array X 4
37	12.3	38.5	26.7	10.4	19.0
38	34.7	43.6	62.9	26.6	64.5
39	58.4	24.1	28.2	14.1	41.2
40	59.5	48.6	15.5	29.4	39.4
41	47.8	63.2	48.7	9.1	77.4
42	31.7	34.9	46.2	76.9	31.0
43	40.7	31.3	30.3	50.6	62.3
Sheet1

Cell Formulas
Range	Formula
B13:F21	B13	=B2*2
B24:F32	B24	=B2*3
B35:F43	B35	=B2*4

My current code to replace the formula approach...although I think I've messed up more than just the spacing at this point with numCalcs.

Option Explicit
'*************************************************************************
'*Calc from Originals                                  *
'*************************************************************************
Sub FirstCalc()
    
    Dim inarr, outaar As Variant
    Dim lastCol, lastRow, i, j, numCalcs As Long
    Dim row, col As Range

    Dim iSh As Worksheet
    Dim oSh As Worksheet
    
    Set iSh = Worksheets("Sheet2")
    Set oSh = Worksheets("Sheet2")
    
    With iSh
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, 2).End(xlUp).row
        inarr = Range(Cells(2, 2), Cells(lastRow, lastCol))
    End With
    
    ReDim outarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
    
        For numCalcs = 2 To 5
            
            For i = 1 To UBound(inarr, 1)
                For j = 1 To UBound(inarr, 2)
                    outarr(i, j) = inarr(i, j) * numCalcs
                Next j
            Next i
            
                With oSh
                    .Range("B" & lastRow + 2).Resize(UBound(outarr, 1), UBound(outarr, 2)).Value = outarr
                End With
            
            numCalcs = numCalcs + 1
            
        Next numCalcs
    
End Sub

 

[GeeWhiz7]

Hi folks,
Yet another thing I seem to be stuck on. Code and mini-sheet below to illustrate what I'm trying to do.

Issue:
I have an input array that is not always a fixed size. I would like to do some match on the items in that array and write out the result below the previous array with a space between.
This works fine if I calculate the last row and use it +2 to position the first output array by itself. But, I'd like to keep doing this with multiple arrays and then my approach starts to mess up and I can't quite spot why.

Goal:
Be able to do as many new arrays as I want, each stacked neatly below the previous with a space or two while still allowing for dynamic sizes of the input array.

Example Desired Result just using formulas in sheet

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	B	C	D	E
2	10.7	16.0	7.2	19.3	11.3			Original Inputs
3	4.3	14.1	5.0	15.8	19.1			These data array can change size by column and row
4	3.1	9.6	6.7	2.6	4.8
5	8.7	10.9	15.7	6.6	16.1
6	14.6	6.0	7.1	3.5	10.3
7	14.9	12.2	3.9	7.3	9.9
8	11.9	15.8	12.2	2.3	19.4
9	7.9	8.7	11.6	19.2	7.7
10	10.2	7.8	7.6	12.7	15.6
11
12	A	B	C	D	E			1st Calculation
13	21.3	32.0	14.4	38.7	22.7			Would like to have this calculation with one row space from original
14	8.6	28.1	10.0	31.7	38.1			=Original Array X 2
15	6.2	19.2	13.4	5.2	9.5
16	17.3	21.8	31.5	13.3	32.2
17	29.2	12.0	14.1	7.1	20.6
18	29.7	24.3	7.8	14.7	19.7
19	23.9	31.6	24.3	4.6	38.7
20	15.8	17.4	23.1	38.4	15.5
21	20.3	15.7	15.1	25.3	31.1
22
23	A	B	C	D	E			2nd Calculation
24	32.0	48.0	21.6	58.0	34.0			Same with each subsequent calculation would like one row space
25	12.9	42.2	15.0	47.5	57.2			=Original Array X 3
26	9.2	28.9	20.1	7.8	14.3
27	26.0	32.7	47.2	19.9	48.4
28	43.8	18.1	21.2	10.6	30.9
29	44.6	36.5	11.6	22.0	29.6
30	35.8	47.4	36.5	6.8	58.1
31	23.7	26.2	34.7	57.7	23.2
32	30.5	23.5	22.7	38.0	46.7
33
34	A	B	C	D	E			2nd Calculation
35	42.7	64.0	28.8	77.3	45.3			Same with each subsequent calculation would like one row space
36	17.2	56.2	20.0	63.3	76.3			=Original Array X 4
37	12.3	38.5	26.7	10.4	19.0
38	34.7	43.6	62.9	26.6	64.5
39	58.4	24.1	28.2	14.1	41.2
40	59.5	48.6	15.5	29.4	39.4
41	47.8	63.2	48.7	9.1	77.4
42	31.7	34.9	46.2	76.9	31.0
43	40.7	31.3	30.3	50.6	62.3
Sheet1

Cell Formulas
Range	Formula
B13:F21	B13	=B2*2
B24:F32	B24	=B2*3
B35:F43	B35	=B2*4

My current code to replace the formula approach...although I think I've messed up more than just the spacing at this point with numCalcs.

Option Explicit
'*************************************************************************
'*Calc from Originals                                  *
'*************************************************************************
Sub FirstCalc()
   
    Dim inarr, outaar As Variant
    Dim lastCol, lastRow, i, j, numCalcs As Long
    Dim row, col As Range

    Dim iSh As Worksheet
    Dim oSh As Worksheet
   
    Set iSh = Worksheets("Sheet2")
    Set oSh = Worksheets("Sheet2")
   
    With iSh
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, 2).End(xlUp).row
        inarr = Range(Cells(2, 2), Cells(lastRow, lastCol))
    End With
   
    ReDim outarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
   
        For numCalcs = 2 To 5
           
            For i = 1 To UBound(inarr, 1)
                For j = 1 To UBound(inarr, 2)
                    outarr(i, j) = inarr(i, j) * numCalcs
                Next j
            Next i
           
                With oSh
                    .Range("B" & lastRow + 2).Resize(UBound(outarr, 1), UBound(outarr, 2)).Value = outarr
                End With
           
            numCalcs = numCalcs + 1
           
        Next numCalcs
   
End Sub

This change makes it work. Turns out I needed to re assign the lastRow variable after each array iteration.

Option Explicit
'*************************************************************************
'*Calc from Originals                                  *
'*************************************************************************
Sub FirstCalc()
    
    Dim inarr, outaar As Variant
    Dim lastCol, lastRow, i, j, numCalcs As Long
    Dim row, col As Range

    Dim iSh As Worksheet
    Dim oSh As Worksheet
    
    Set iSh = Worksheets("Sheet2")
    Set oSh = Worksheets("Sheet2")
    
    With iSh
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, 2).End(xlUp).row
        inarr = Range(Cells(2, 2), Cells(lastRow, lastCol))
    End With
    
     ReDim outarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
     For numCalcs = 2 To 5
     
            For i = 1 To UBound(inarr, 1)
                For j = 1 To UBound(inarr, 2)
                    outarr(i, j) = inarr(i, j) * numCalcs
                Next j
            Next i
            
            With oSh
                .Range("B" & lastRow + 2).Resize(UBound(outarr, 1), UBound(outarr, 2)).Value = outarr
            End With
            
            lastRow = iSh.Cells(Rows.Count, 2).End(xlUp).row  '##Added this line so after each new array is written out the next "last Row" would be updated.

        Next numCalcs
    
End Sub

 

[BSALV]

i don't understand what you want

Sub multiply()

     Set iSh = Worksheets("Sheet2")
     With iSh
          lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
          lastRow = .Cells(Rows.Count, 2).End(xlUp).row
          Set c = .Range(.Cells(2, 2), .Cells(lastRow, lastCol))
     End With

     For i = 2 To 5
          With c.Offset((c.Rows.Count + 2) * (i - 1))
               .Formula = "=" & c.Cells(1, 1).Address(0, 0) & "*" & i
               .Value = .Value
          End With
     Next

End Sub

 

[GeeWhiz7]

i don't understand what you want

Sub multiply()

     Set iSh = Worksheets("Sheet2")
     With iSh
          lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
          lastRow = .Cells(Rows.Count, 2).End(xlUp).row
          Set c = .Range(.Cells(2, 2), .Cells(lastRow, lastCol))
     End With

     For i = 2 To 5
          With c.Offset((c.Rows.Count + 2) * (i - 1))
               .Formula = "=" & c.Cells(1, 1).Address(0, 0) & "*" & i
               .Value = .Value
          End With
     Next

End Sub

Sometimes neither do I! In this case I was wanting each successive array that was coming out of the For loop to be placed directly beneath the previous one with one blank row in between. I finally figured out that I was not updating the lastRow variable properly after each loop iteration so it was doing weird things by using the previous value. Actually there were two errors, that one and I was double incrementing my numCalcs variable by using numCalcs +1 and Next numCalcs. Still getting used to VBA...

Your code works too so I will bank that as well for another use. thank you for taking a look!