substracting two cells

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
984
Office Version
  1. 2010
Platform
  1. Windows
I want to calculate the range of some rows, for example substract cell G1-B1 and the result in J. I didn't found nothing simple on internet. maybe something as Range G1 as integer
Range B1 as integer , I have code generate thousands of combination so I want the range only.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
montecarlo2012 said:
I want to calculate the range of some rows, for example substract cell G1-B1 and the result in J. I didn't found nothing simple on internet. maybe something as Range G1 as integer
Range B1 as integer , I have code generate thousands of combination so I want the range only.
Click to expand...

Your question is not entirely clear to me, but is sounds like you may want this...

Code: 
Range("J1").Value = Range("G1").Value - Range("B1").Value
 
Upvote 0
Thanks, Rick Rothstein. What I am trying to do is to know the statistic range of a list of numbers I have in B1:G1998 and you know range is just the difference between the major number and the minor so in my list the major is on G1 all the way down and still increase weekly so how to put in the range ("J1") to tell --- all the way down , in other words whatever I write in B or G the entire column.
 
Upvote 0
montecarlo2012 said:
Thanks, Rick Rothstein. What I am trying to do is to know the statistic range of a list of numbers I have in B1:G1998 and you know range is just the difference between the major number and the minor so in my list the major is on G1 all the way down and still increase weekly so how to put in the range ("J1") to tell --- all the way down , in other words whatever I write in B or G the entire column.
Click to expand...
Try it like this...
Code: 
Range("J1").Value = Cells(Rows.Count, "G").End(xlUp).Value - Range("B1").Value
 
Upvote 0
something like this
Code: 
[TABLE="width: 480"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"][FONT=Calibri]21[/FONT]
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]11[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]22[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]23[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]13[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]24[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]25[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]26[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]16[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]27[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]17[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]28[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]29[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]30[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]20[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]31[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri]32[/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]22[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
montecarlo2012 said:
Sir, Rothstein, this time do not work.
Click to expand...

Assuming date in B1:G1998, are you saying the code line I posted is not giving you the difference in values between what is in G1998 and B1? It works fine for me when I test it here on my Excel worksheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
215
sspatriots
S
L
  • Solved
Result from a 2 factors research
Replies
7
Views
329
Loadlucas
L
Y
  • Solved
Selecting diagonal range
Replies
3
Views
230
Yamasaki450
Y
JeffGrant
  • Solved
VBA Code used twice, keeps table formatting on one sheet, but not on the other
Replies
4
Views
289
smozgur
smozgur
S
  • Question
Macro to change one cell on a sheet, save sheet as PDF, then loop back and change that one cell on that sheet and repeat until the end
Replies
0
Views
68
SMRXIV
S

Forum statistics

Threads
1,216,349
Messages
6,130,136
Members
449,560
Latest member
mattstan2012

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