Loop - dynamic arrays - different worksheets

[montecarlo2012]

Hello Everybody. PLEASE, I have two sheets and one dynamic array in each one, what I need is to subtract row by row one from another.
On sheet 1 my array is for example purpose A1:F30, and on sheet 2 B2:G30 [in my workbook each are 6 by 2521 and growing].
this is the macro for better understanding purpose:

Sub Macro1()
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]-Sheet2!R[1]C[-6]"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H23"), Type:=xlFillDefault
    Range("H1:H23").Select
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]-Sheet2!R[1]C[-6]"
    Range("I1").Select
    Selection.AutoFill Destination:=Range("I1:I23"), Type:=xlFillDefault
    Range("I1:I23").Select
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]-Sheet2!R[1]C[-6]"
    Range("J1").Select
    Selection.AutoFill Destination:=Range("J1:J23"), Type:=xlFillDefault
    Range("J1:J23").Select
End Sub

Really appreciate the time you've taken out of your schedule to help me.

 

[pgc01]

Hi

You wrote dynamic arrays in your post but you did not post anything dynamic.

Your example is completely static.

This is an example similar to what you posted: just subtracting 2 ranges.

Sssuming

r1: =Sheet1!A1:F30
r2: =Sheet2!B1:G30
r3: =Sheet3!C1:H30

and you want

r3=r1-r2

Try:

Sub Test()

Worksheets("Sheet3").Range("C1:H30").Value = [Sheet1!A1:F30-Sheet2!B1:G30]

End Sub

 

[montecarlo2012]

Sorry and Thank you for your time. what I upload said is "just a little example", I didn't upload the real workbook. and again thank you, I will be trying the line.

 

[montecarlo2012]

I know you are completely right, I didn't explained properly. So here we go.I am uploading "partial information only"
Sheet 1

	A	B	C	D	E	F
1	6	9	16	29	33	52
2	17	26	30	40	45	49
3	2	6	8	22	35	52
4	5	23	24	34	36	41
5	11	16	21	27	30	31
6	7	15	18	30	47	51
7	1	6	41	49	50	51
8	1	16	18	32	40	48
9	1	7	9	10	21	48
10	4	14	22	30	44	48
11	3	15	24	27	37	50
12	3	28	31	38	39	51
13	1	2	14	26	32	38
14	5	7	26	30	37	46
15	11	19	24	43	48	49
16	16	19	21	37	50	51
17	15	21	32	40	41	46
18	10	27	28	30	45	51
19	4	17	20	21	26	39
20	10	12	20	29	38	49
21	1	15	16	24	33	52
22	1	8	22	24	27	50
23	5	19	20	24	27	51

<tbody>
</tbody>

 

[montecarlo2012]

sheet 1 minus sheet 2 row by row

	A	B	C	D	E	F
1	11	17	14	11	12	3
2	15	20	22	18	10	3
3	3	17	16	12	1	11
4	6	7	3	7	6	10
5	4	1	3	3	17	20
6	6	9	23	19	3	0
7	0	10	23	17	10	3
8	0	9	9	22	19	0
9	3	7	13	20	23	0
10	1	1	2	3	7	2
11	0	13	7	11	2	1
12	2	26	17	12	7	13
13	4	5	12	4	5	8
14	6	12	2	13	11	3
15	5	0	3	6	2	2
16	1	2	11	3	9	5
17	5	6	4	10	4	5
18	6	10	8	9	19	12
19	6	5	0	8	12	10
20	9	3	4	5	5	3

<colgroup><col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

and the answers I expect are

I	J	K	L	M	N
5	8	2	18	21	49
2	6	8	22	35	46
1	11	8	10	34	41
1	16	21	27	30	31
7	15	18	24	13	11
1	6	5	11	44	51
1	4	18	32	40	48
1	7	9	10	21	48
2	0	4	10	2	48
3	13	20	27	37	46
3	2	17	16	35	49
1	2	14	26	32	38
3	3	2	22	27	30
1	5	24	17	26	43
6	19	21	37	46	47
15	17	10	34	41	46
10	15	28	30	37	41
4	17	20	21	26	39
2	12	20	13	14	29
1	9	16	24	33	46

<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

 

[montecarlo2012]

and this is the code I am working on

Sub addrows()
Dim x As Long
Dim y As Long
    For x = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        For y = 2 To 7
            Worksheets("Sheet2").Range("H1:M30").Value = [Sheet1!B1:G30-Sheet2!A1:F30]
        Next y
    Next x
    
 End Sub

 

[montecarlo2012]

so my real question is: instead of using regular range references ("H1:M30") I am looking for something like ►cells(rows.count,___).End(xlDown or up).Row . something like this.

 

[montecarlo2012]

I am talking for example also, with this line ►Sheet3.Cells(x - 1, y - 1) = Abs(Cells(x, y) - Cells(x, y + 1))◄ is possible to subtract column by column to the end of the rows, but this is in the same worksheet, the question is how to do that in two different worksheets and the answer in one of them.
Thank you for your time.

 

[offthelip]

A much faster more efficient way of doing this is to use variant arrays . VBA is very slow at accessing the worksheet so looping through the worksheet accessing the sheet multiple times every loop is going to very slow. It is Ok for a few hundred interatiosn but as soon as you get into 1000 it will be slow. However loading and entire sheet into memeory as a variant array and then operaitng in memory is much faster.
You specification is ambigous becuase in your first post you said sheet 1 was A to F ansd sheet 2 B to G while your code in post #6 is the opposite.. So this code baiscally doesit but hte columns and shets could be wrong. ( Note untested)

Sub test()Dim outarr As Variant


With Worksheets("sheet1")
  lr1 = .Cells(Rows.Count, "A").End(xlUp).Row
  sht1 = Range(.Cells(1, 1), .Cells(lr1, 6))
End With
With Worksheets("sheet2")
  lr2 = .Cells(Rows.Count, "B").End(xlUp).Row
  sht2 = Range(.Cells(1, 2), .Cells(lr2, 7))
ReDim outarr(1 To lr1, 1 To 1)
For i = 2 To lr1
 For j = 1 To 6
    outarr(i, 1) = sht1(i, j) - sht2(i, j)
 Next j
Next i
 Range(.Cells(1, 8), .Cells(lr1, 8)) = outarr


End With


End Sub

 

[pgc01]

I see in your post 1 thing that I don't understand: you have 23 rows in the table in sheet1 and the 2 tables in Sheet2 have 20 rows, also the number of columns is not equal

As a first step, I'll assume

1 - all tables have the same number of rows and columns
2 - the 2 tables you are subtracting start at Sheet1!A1 and Sheet2!A1 and are surrounded by empty cells
3 - the result table starts at Sheet2!H1

Using your data under these assumptions, this is another solution:

Sub addrows()
Dim rTable1 As Range, rTable2 As Range, rTable3 As Range

' define the tables ranges
Set rTable1 = Worksheets("Sheet1").Range("A1").CurrentRegion
Set rTable2 = Worksheets("Sheet2").Range("A1").CurrentRegion
Set rTable3 = Worksheets("Sheet2").Range("I1").Resize(rTable1.Rows.Count, rTable1.Columns.Count)

rTable3.Value = Evaluate("index(" & rTable1.Address(external:=True) & "-" & rTable2.Address(external:=True) & ",0)")

End Sub