Comparing two adjacent numbers throughout a column

[JennyBorlin]

Hi!
I am new to both Excel and vba. I have data for a test, where the current is logged in one column. In the beginning of the test, the current is low, after a while the current has risen (middlestage), and at the end of the test it is going to zero.

How do I compare two adjacent numbers througout a column?
Can I use the name of a cell as a variable in code? That is, calculate a variable with the with the same name as the cell and get the value to that cell.

What I want, is to get the row (Testrowstart) where the current has risen and the test is in its middlestage. In order to do this, I have written the code below. The code is supposed to get the absolute difference between to adjacent numbers in a column, throughout the column, and check if the difference is greater than a certain number, and then return the row number where this happens.
When I try the code, I have a cell that I renamed to Testrowstart where I put my user defined function. But I get #VALUE !, so what am I doing wrong?

Thanks!

' 2018-03-19
'
' Returns the row where the test enters middle stage
'
'
Function Testrowstart(WorkRng As Range) As Integer


'Testrowstart = 0


Dim column As Variant
column = WorkRng.Value


' Test two values at the time in whole column
' If the difference is larger than 15, the test has entered middle stage


For i = 1 To UBound(column, 1)
    ' If difference between element i and (i-1) is larger than 15, set
    ' Testrowstart to i
        
    If i > 1 Then
        If VBA.Abs(column(i) - column(i - 1)) > 15 Then
            Testrowstart = i
        End If
    End If
 Next


End Function

 

[steve the fish]

Hi. Unless you need code you can do that with a formula:

=MATCH(1,INDEX(--(ABS(A:A-B:B)>15),0),0)

 

[MickG]

Try this:-

Function MidRws(Rng [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, temp [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 [COLOR="Navy"]If[/COLOR] Not temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Abs(Dn.Value) - temp > 15 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(temp, Dn)
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, temp, Dn)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Set[/COLOR] temp = Dn
[COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    nStr = Replace(Dn.Address, ":", "-")
    Msg = Msg & "Rows :=" & Evaluate(Split(Dn.Address, ":")(0)).Row _
    & "/" & Evaluate(Split(Dn.Address, ":")(1)).Row _
    & "  Count := " & Abs(Evaluate(nStr)) & vbLf
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
MidRws = Msg
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Regards Mick

 

[JennyBorlin]

Thanks, I'll try it. But I cant figure out what the range is in the abs function...The range in the column I want to use is G2 to G114. Will the A:A-B:B be G2:G2-G114:G114? When I tried the formula I also get "Invalid" as I fill in the second argument.

 

[JennyBorlin]

Thanks for the code, Mick! It may work if I change some things. I tried it and it works fine for the intended column. But when I try it for another, it returns a number that I don't expect. I wonder if it might have to do with the variable temp not being absolute.
When I try the code for this column, it returns the fourth row which is correct:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]-0,232[/TD]
[/TR]
[TR]
[TD="align: right"]-0,234[/TD]
[/TR]
[TR]
[TD="align: right"]-0,234[/TD]
[/TR]
[TR]
[TD="align: right"]-25,55[/TD]
[/TR]
[TR]
[TD="align: right"]-25,628[/TD]
[/TR]
</tbody>[/TABLE]
But when I try for this one, it returns the second, even though the condition is set to 15:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]-11,625[/TD]
[/TR]
[TR]
[TD="align: right"]-11,725[/TD]
[/TR]
[TR]
[TD="align: right"]-11,725[/TD]
[/TR]
[TR]
[TD="align: right"]-1251,95[/TD]
[/TR]
[TR]
[TD="align: right"]-1252,72[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Do the commas in your numbers represent decimal points ???

In your data could you have a series of sequential numbers that each have a difference greater than 15, say 10,30,60,120,130, and if that where the case would you want to return the last in the series.(60,120)

 

[JennyBorlin]

Mick, yes the commas represent decimal points. The test data is such that, yes, I would want to return the last in the series. It is quite improbable though, since discharging the batteries will cause the voltage to drop. The columns above are just the few first rows that I pasted in and one test reaches over a hundred rows. For the time being, I have concentrated only on the transition from start stage to middle stage of the test.