From macros to vba (substractions)

M

matroids

New Member
Joined
Oct 8, 2012
Messages
9
Hi, everybody Happy new year to all.

I would like some code for some substraction that I need.
Code: 
[TABLE="width: 423"]
 <colgroup><col style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" width="32"> <col style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;" span="6" width="21"> <col style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;" width="41"> <col style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;" span="17" width="21"> <tbody>[TR]
  [TD="class: xl63, width: 32, bgcolor: #0070C0"][FONT=Calibri] [/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]A[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]B[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]C[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]D[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]E[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]F[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 41, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]G[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]H[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]I[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]J[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]K[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]L[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]M[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]N[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]O[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]P[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]Q[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]R[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]S[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]T[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]U[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]V[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl64, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]W[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl65, width: 21, bgcolor: #8DB4E2"][FONT=Calibri][SIZE=3][COLOR=#000000]X[/COLOR][/SIZE][/FONT][/TD]
 [/TR]
 [TR]
  [TD="class: xl66, bgcolor: #8DB4E2, align: right"][FONT=Calibri]1[/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]20[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl67, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl68, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
 [/TR]
 [TR]
  [TD="class: xl69, bgcolor: #8DB4E2, align: right"][FONT=Calibri]2[/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]4[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl70, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT][/TD]
  [TD="class: xl71, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT][/TD]
 [/TR]
</tbody>[/TABLE]
as you see I have six values A:F and I want to substract a series of numbers against this row, I need to start with 20 minus each one on A:F, then 19, 18 etc. until 1 using =ABS(). and this is the macro:
Code: 
Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveCell.FormulaR1C1 = "=ABS(R[-1]C-RC[-7])"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:M2"), Type:=xlFillDefault
    Range("H2:M2").Select
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=ABS(R[-1]C-RC[-13])"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:R2"), Type:=xlFillDefault
    Range("N2:R2").Select
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=ABS(R[-1]C-RC[-18])"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:X2"), Type:=xlFillDefault
    Range("S2:X2").Select
End Sub
I need to see the transformation from the simple -- Range ("H2").Value = Range("H1").Value - Range("A2").Value
to this loop situation.
THANKS.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
I still doesn't sure of what you want, but is it OK to do sth like this? (u don't even need VBA)
142127314552
61711253220
131812861319
5061012518
121711751217
415911416
111410641115
304810314
10139531013
21379212
910842911
10268110
8973189
0115708
7662077
1004616
4551145
0113304
3220033
1002212
0111101



<colgroup><col style="width: 54pt;" span="7" width="72">
<tbody>


</tbody>
 
Upvote 0
matroids,


Sample raw data:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXY
1201918
2142127314552
3
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXY
1201918
2142127314552617112532528122633439132734
3
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub matroids()
' hiker95, 12/29/2012
' http://www.mrexcel.com/forum/excel-questions/676669-macros-visual-basic-applications-substractions.html
Application.ScreenUpdating = False
Cells(2, 8).Resize(, 6).FormulaR1C1 = "=ABS(R1C8-R2C[-7])"
Cells(2, 14).Resize(, 6).FormulaR1C1 = "=ABS(R1C14-R2C[-13])"
Cells(2, 20).Resize(, 6).FormulaR1C1 = "=ABS(R1C20-R2C[-19])"
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the matroids macro.
 
Last edited:
Upvote 0
Hi,

Please try this:
Code: 
Option Explicit

Sub Set_formulas()
    Dim Col As Integer
    Dim Col_min1 As Integer
    Dim Col_min2 As Integer
    Dim Subt As Integer
    Dim Top As Integer
    
    Col = 8     'Start column
    'Count down 20..1
    For Top = 20 To 1 Step -1
        'Setup subtraction formula
        Col_min1 = Col
        Col_min2 = -1 * Col + 1
        For Subt = 1 To 6
            Cells(2, Col).Activate
            Cells(2, Col).FormulaR1C1 = "=ABS(R[-1]C[" & (Col_min1 - Col) & "]-RC[" & (Col_min2) & "])"
            Col = Col + 1
        Next Subt
    Next Top
End Sub

Note: the numbers you gave in the example are not correct according to you description!
Only the number directly below the numbers at row 1 are correct, I think.

Succes,

Paul
 
Upvote 0
matroids,


Your R1C1 formulae were not correct.


Sample raw data (per your example - my row 1 columns were not correct, but are correct now):


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1201918
2142127314552
3
Sheet1





After the updated macro:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1201918
21421273145526171125325281226439132734
3
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub matroids_V2()
' hiker95, 12/29/2012
' http://www.mrexcel.com/forum/excel-questions/676669-macros-visual-basic-applications-substractions.html
Application.ScreenUpdating = False
Cells(2, 8).Resize(, 6).FormulaR1C1 = "=ABS(R1C8-R2C[-7])"
Cells(2, 14).Resize(, 5).FormulaR1C1 = "=ABS(R1C14-R2C[-13])"
Cells(2, 19).Resize(, 6).FormulaR1C1 = "=ABS(R1C19-R2C[-18])"
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the matroids_V2 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
408
kvsrinivasamurthy
K
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
178
gtd526
gtd526
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
181
Tetra201
Tetra201
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
172
dilshod_k
D
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
534
kevin9999
K

Forum statistics

Threads
1,215,652
Messages
6,126,041
Members
449,281
Latest member
redwine77

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