substract one row

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
986
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone.
trying to write a code to substract the row A:F from each row of I:N and the corresponding results for each one on P:U
I ask vba code instead of formula because I have to do this so many times a week. and my list in I:N is more or less I2:N500. any hand on this welcome. looking for absolute value .
Code: 
[TABLE="width: 242"]
<tbody>[TR]
[TD="class: xl63, width: 24, bgcolor: #D9D9D9"][FONT=Calibri]A[/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]B[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]C[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]D[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]E[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]F[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #D9D9D9"][/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]I[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]J[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]K[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]L[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]M[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #D9D9D9"][FONT=Calibri][SIZE=3][COLOR=#000000]N[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]12[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]48[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]22[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]24[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]35[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]44[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]33[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]40[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]16[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]38[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]49[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]25[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]13[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]44[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]46[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]
thanks.
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Ok so how do you get the numbers 12,12,48,22,27,1
from the data in I:N...I don't see the number 48 anywhere in the right hand block of data
 
Upvote 0
Code: 
Sub mc2012()
   With Sheets("sheet1")
    Range("I1:N" & Range("N" & Rows.Count).End(xlUp).Row).Copy Range("P1")
    Range("A1:F1").Copy
    Range("P1:U" & Range("N" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract
    End With
End Sub
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Oct05
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] ColRng      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("I1"), Range("I" & Rows.Count).End(xlUp)).Resize(, 6)
[COLOR="Navy"]Set[/COLOR] ColRng = Range("A1:F1")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Rng.Rows
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Rw.Columns
            Ac = Ac - ColRng(, Ac.Column - 8)
            Ac.Offset(, 7) = Ac.Offset(, 7) - ColRng(, Ac.Column - 8)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Michael.
All this numbers are random list; I hard do it one by one thats why I ask for help with some code. I had been using =abs(A1-I1) drag this and etc; I know must be a better way with vba.
Really here is not about where the numbers coming from, is about a substraction,
and because is not couple numbers involved, is really complicated for me.
 
Upvote 0
Here's my attempt.
Code: 
Sub Subtract_Row()
  Dim a
  Dim i As Long, j As Long, rws As Long
  
  With Range("A1", Range("I" & Rows.Count).End(xlUp)).Resize(, 21)
    a = .Value
    rws = UBound(a, 1)
    For i = 1 To rws
      For j = 1 To 6
        a(i, 15 + j) = Abs(a(1, j) - a(i, 8 + j))
      Next j
    Next i
    .Value = a
  End With
End Sub

For the sample data, this is the result of my code.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUV
1121248222717243242123551216201534
215323644153332012221232
3401112639281147102138
41651384925514391027250
5314211321211930479620
644545141231327381530
7426306143930618161338
84773242461835516201917
9
Subtract Row
 
Upvote 0
Thanks Peter_SSs.

For hipiehacker I can tell you that, in the first place I must post more information, now I realize the worksheet mr Peter upload is the way I must did from the very beginning, that sheet is exactly what I am looking for.
All of you count for me, is an effort, time and work thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
81
djclements
djclements
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
256
Peter_SSs
Peter_SSs
A
  • Solved
Two-value combination extraction formula?
2
Replies
11
Views
188
bebo021999
B
S
  • Solved
Individual Ranking
2
Replies
11
Views
455
sksanjeev786
S
J
  • Solved
#N/A Error
2
Replies
14
Views
354
Jedi Master
J

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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