0

I have numerous excel sheets that contain rows that have paired data. Specifically, I need to subtract the first row from the one that follows (e.g., row 2-row 1; row 4-row3; etc.) and place the result into a new row below each pair. My data in each sheet appear as follows:

Raw Data

I am not new to programming languages, but I am new to visual basic.

My current code is:

Sub test() Dim rng As Range
Columns(1).Insert
With Range("b2", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
    .Formula = "=if(mod(row(),2)=1,1,"""")"
    .Value = .Value
    .SpecialCells(2, 1).EntireRow.Insert
End With
Columns(1).Delete
With Range("a1", Range("a" & Rows.Count) _
        .End(xlUp)(2)).Resize(, 3)
    .Columns(1).SpecialCells(4).Value = "Difference"
    Union(.Columns(2).SpecialCells(4), .Columns(3) _
    .SpecialCells(4)).Formula = _
    "=r[-1]c-r[-2]c"
End With
End Sub

However, the result is this:

Result

I am mainly interested in calculating the differences between row pairs in the first column shown, but it is clearly not working.

Any help would be greatly appreciated!

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
colclar
  • 3
  • 3
  • 1
    You want to start at the bottom, not at the top of the table. There are several answers on finding the last row in a table. [Here is one](https://stackoverflow.com/questions/64422612/to-find-the-last-used-row-count-in-every-column-using-excel-vba) – cybernetic.nomad Jun 15 '22 at 17:59
  • I would try a `for` loop with `step -2`. – findwindow Jun 15 '22 at 18:02
  • I'm sorry, I'm not quite sure how to go about doing that. Could you possibly show me in the context of my existing code? If you have a moment to spare. – colclar Jun 15 '22 at 19:03

1 Answers1

0

Easier to use formulae, rather than VBA.
Go to a second sheet in the file ("Sheet2")
Enter in A1: =Sheet1!A1-Sheet1!A2
On Sheet2, select Rows1 AND 2.
Drag down.

Then depends on what you need to do.
May be Copy | Paste Special | Values to Sheet3, and sort to remove blank rows.

zsalya
  • 454
  • 4
  • 8
  • Unfortunately I have about 150 of these sheets, so that isn't an option. I appreciate it though! – colclar Jun 16 '22 at 13:28
  • Do the answers need to be in those 150 sheets too? If not, how about using VBA to just grab the data and write to a CSV file, from where you can import to a database or use a language with which you are more familiar? – zsalya Jun 17 '22 at 18:32