Subtraction macro request ...

Hey guys.

I have Time Data in one column, and Values in the next, and then a blank column (for calculations, like subtraction/time).

Is there any way to automate the =Cell2-Cell1 command for regions of blue cells and have it paste the value in the blank column lined up with the first blue cell of the region?

The attachment will make more sense, but this is what I'm talking about:

Before subtraction macro:

BlueTime1 Value Blank BlueTime4 Value Blank
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value Blank BlueTime10 Value Blank
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank

After subtraction macro:

BlueTime1 Value BlueTime3-BT1 BlueTime4 Value BlueTime6-BT4
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value BlueTime9-BT7 BlueTime10 Value BlueTime12-BT10
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank

Sample attached.

Thanks so much guys!

Hi

This is pretty clunky but should get you there.

Sub aaa()
  Range("C:C,F:F,I:I").ClearContents
  
  endrow = Cells(Rows.Count, 1).End(xlUp).Row
  For i = endrow To 3 Step -1
    If IsEmpty(Cells(i - 1, 1)) Then
      Cells(i, 3).Formula = "=A" & endrow & "-A" & i
      endrow = i - 2
    End If
  Next i
  
  endrow = Cells(Rows.Count, 4).End(xlUp).Row
  For i = endrow To 3 Step -1
    If IsEmpty(Cells(i - 1, 4)) Then
      Cells(i, 6).Formula = "=D" & endrow & "-D" & i
      endrow = i - 2
    End If
  Next i
  
  endrow = Cells(Rows.Count, 7).End(xlUp).Row
  For i = endrow To 3 Step -1
    If IsEmpty(Cells(i - 1, 7)) Then
      Cells(i, 9).Formula = "=G" & endrow & "-G" & i
      endrow = i - 2
    End If
  Next i
End Sub

You will have to make sure that the breaks in the driving columns (A,D,G) are blanks, not the spaces that you currently have in some of the cells. Also, row 2 will also have to be blank. As an example A13 is blank, but A9 has a space.


HTH

rylo

Hey.

Cool, I'll try it. Now is this going to work for a spreadsheet that has more of the same stuff (more rows and more columns), with the same format?

Thanks

Hi

Number of rows doesn't matter, but it will only operate on the set number of columns.

If you are likely to have a variable number of columns, let me know and I'll rejig things a bit.


rylo