Subtracting last two columns of data (Super hard)

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I am trying to get the net change between the last two columns of data I have. The only problem is that each week my data extends a column because weekending data comes in. So I can't make any cell references in my macro when subtracting because I need to extend to the next column the next week. Is there something in VB that figure out the last two columns of data that I have in a sheet and then subtract the last from the previous in the next column over.
for ex. If my last two columns of data are range B5:C100, I need to get the difference between those two columns in range D5:D100. However, the following week, the last two columns of data will be in range C5:D100 then I will need the difference in range E5:E100. Make sense, I hope. thanks in advance.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try this.

Code:
Sub aaa()
  lastcol = Cells(5, Columns.Count).End(xlToLeft).Column
  lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row
  
  For Each ce In Range(Cells(5, lastcol), Cells(lastrow, lastcol))
    ce.Offset(0, 1).Value = ce.Value - ce.Offset(0, -1).Value
  Next ce
  
End Sub

HTH

Tony
 

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
thank you so so much, i hate to be a pest, but is there anyway that you can copy over the format from the previous column. I'm getting the difference, but it needs to be in the same format as the two columns that I'm pulling from. And one more quick question....totally unrelated, can VB search for any cell with the word "Computer" and delete that entire column. your the best thank u so much
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try

Code:
Sub aaa()
  lastcol = Cells(5, Columns.Count).End(xlToLeft).Column
  lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row
  
  For Each ce In Range(Cells(5, lastcol), Cells(lastrow, lastcol))
    ce.Offset(0, 1).Value = ce.Value - ce.Offset(0, -1).Value
  Next ce
  
  Columns(lastcol).Copy
  Columns(lastcol + 1).PasteSpecial (xlPasteFormats)
  
  Application.CutCopyMode = False
  
  'remove the computer column
  Set c = Cells.Find(what:="computer", lookat:=xlPart)
  While Not c Is Nothing
    c.EntireColumn.Delete
    Set c = Cells.Find(what:="computer", lookat:=xlPart)
  Wend
End Sub

Tony
 

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663

ADVERTISEMENT

Hey Smitty.. One more thing...Is it possible to put the words "Net Change" in the cell above the differnce column..?? Thanks i really appreciate it ..
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Code:
Sub aaa() 
  lastcol = Cells(5, Columns.Count).End(xlToLeft).Column 
  lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row 
  
  For Each ce In Range(Cells(5, lastcol), Cells(lastrow, lastcol)) 
    ce.Offset(0, 1).Value = ce.Value - ce.Offset(0, -1).Value 
  Next ce 
  
  Columns(lastcol).Copy 
  Columns(lastcol + 1).PasteSpecial (xlPasteFormats) 
  cells(1,lastcol+1).value = "Net Change"


  
  Application.CutCopyMode = False 
  
  'remove the computer column 
  Set c = Cells.Find(what:="computer", lookat:=xlPart) 
  While Not c Is Nothing 
    c.EntireColumn.Delete 
    Set c = Cells.Find(what:="computer", lookat:=xlPart) 
  Wend 
End Sub


Tony
 

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
Thanks Smitty...I guess it is safe to say your a programmer of some sort. If not, I'll be sick to my stomach. Thanks again....
 

Forum statistics

Threads
1,136,427
Messages
5,675,789
Members
419,586
Latest member
RoteichA

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
Top