Subtracting last two columns of data (Super hard)

[davidhall80]

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.

 

[acw]

Hi

Try this.

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]

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]

Hi

Try

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]

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]

Hi

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]

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....