Loop - dynamic arrays - different worksheets

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
986
Office Version
  1. 2010
Platform
  1. Windows
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:
Code: 
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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:

Code: 
Sub Test()

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

End Sub
 
Upvote 0
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.
 
Upvote 0
I know you are completely right, I didn't explained properly. So here we go.I am uploading "partial information only"
Sheet 1
[TABLE="width: 336"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]E
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]F
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]52
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]49
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]52
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]34
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]41
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]31
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]47
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]49
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]32
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]48
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]48
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]44
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]48
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]31
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]38
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]32
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]38
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]46
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]43
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]48
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]49
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]32
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]46
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]38
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]49
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]33
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]52
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
sheet 1 minus sheet 2 row by row

[TABLE="width: 304"]
<colgroup><col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="class: xl65, width: 21, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]E[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


and the answers I expect are

[TABLE="width: 288"]
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]I[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]J[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]K[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]L[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]49[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]51[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]48[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]48[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]48[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]37[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]49[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]38[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]43[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]37[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]37[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]39[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]29[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
and this is the code I am working on
Code: 
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
Code: 
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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
474
Sean15
Sean15
P
  • Question Question
Help with Vba Code
Replies
3
Views
478
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
R
  • Question Question
Formatting using VBA
Replies
0
Views
554
RandyD123
R
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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