Help, My head hurts

[Coopnjaxdad]

This seems like it should be simple, but I can't make it work. I have a spreadsheet taking data through DDE from a NEtDaq and some VB code to move the readings to another sheet and down a row after every scan. That all works fine. I need to calculate the difference between each cell in 2 columns of data, but I don't want to have to do it manually. Can someone please provide some insight?

Thank you.

 

[Domski]

Couple of ways as an example although there are probably much better ways:

This loops down a column doing the calculation and storing it in a variable:

Sub Diff()
Dim lastRow As Long, myRange As Range, mySum As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
mySum = 0
For Each myRange In Range("A1:A" & lastRow)
mySum = mySum + myRange.Offset(0, 1).Value - myRange.Value
Next myRange
Range("E1") = mySum
End Sub

and this creates an array formula to do the calculation:

Sub Diff_Array()
Dim lastRow As Long, myRange As Range, mySum As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("E2").FormulaArray = "=SUM(B1:B" & lastRow & "-A1:A" & lastRow & ")"
End Sub

Both examples take the values in column A from those in B.

Hope it helps,

Dom

 

[Coopnjaxdad]

Thank you, I will give these a shot. Is there a way to do this without using VB and just using a formula?

 

[Coopnjaxdad]

I need to calcualte the difference of each pair of cells and have the value end up in sathe C column. So I need to calculate b2-a2=c2, and so on and so forth all the way dow to b3500-a3500=c3500.

 

[Domski]

My mistake, thought you wanted VB.

This is basically the same array formula as above:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...rl+Shift+Enter. Hope it helps, Dom[/LEFT]

 

[Coopnjaxdad]

No my fault I should have been more clear. Thank you for your help.

 

[Coopnjaxdad]

Odds are I am missing something here but, I don't need to sum the differences, I just need the differences. And I need them shown seperately.

 

[Coopnjaxdad]

Bump Bump.