Difference between start and end rows of consecutive data

[GLIBBY]

Hello. Im trying to write a spreadsheet to accomplish the following function. One column (column a) will be data in which there will be consecutive entires of the same value. There will be a seperate column (column b) of data which is related to the first. I want to create a third column which calculates the difference in the values in column b, which correspond with the first row, and last row of a consecutive block of column A values.

I was thinking the simplest method would be to calculate a given row's difference between the first row of the block. The only problem Im having is determining where a block begins, since the will be multiple occurances of blocks of data.

Any help is much appreciated.

 

[venkat1926]

you data is like this

Excel Workbook
	A	B
1	hdnga	hdngb
2	a	1
3	a	2
4	a	3
5	a	4
6	s	5
7	s	6
8	s	7
9	d	8
10	d	9
11	d	8
12	d	7
13	d	6
Sheet1

now try this macro

Sub test()
Dim r As Range, r1 As Range, c As Range
Set r = Range("A2")
Do
For Each c In Range(r, r.End(xlDown))
If c.Offset(1, 0) <> c Then
Set r1 = c
GoTo Nextstep
End If
Next c
Nextstep:

r.Offset(0, 2) = r1.Offset(0, 1) - r.Offset(0, 1)
Set r = r1.Offset(1, 0)
If r = "" Then Exit Do
Loop
End Sub

Sub undo()
Range("C1").EntireColumn.Delete
End Sub

 

[GLIBBY]

Thats close to what I need, but I was looking to make a function so that I dont have to run the macro each time. I thought about it more and i figured that the function can be in a 3rd column (column c) and can search column A for the next instance of a non continuous value. Then, subtract the linked value at the break from the starting value.

I wrote this function to try and do that, but its not working out for me.

Function continuous(c As Range)

Dim done As Boolean
Dim value As Long, result As Long, r As Long


r = Range(c).Offset(0, 1).value
done = False


Do


 If c.Offset(1, 0) = c And c.Offset(2, 0) = c.Offset(1, 0) Then
    c.Offset(1, 0).Select
    c = Range(ActiveCell)
    done = False
 
 Else
 
 value = c.Offset(1, 1)
 result = value - r
 done = True
 
 End If
 

 
    
Loop Until done = True


continuous = result

End Function

 

[GLIBBY]

Any thoughts? I figure all I need to do is create a function where you declare a cell, and it finds the last cell that is equal to the declared cell, with consecutive values in between. This seems easier said than done though.

 

[gregtx81]

Your function needs to be declared like so:

Function continuous(ByVal c As Range)

I think this works:

Function mycontinuous(ByVal cell As Range)

    Set rvala = cell.Offset(0, -1)
    
    
    vala = rvala.value
    
    Do While vala = rvala.value
        Set rvala = rvala.Offset(1)
    Loop

    mycontinuous = rvala.Offset(-1, 1).value - cell.value


End Function

 

[GLIBBY]

Thats awesome! Exactly what I needed, thanks!

What does the "ByVal" declaration of the function do?

 

[mikerickson]

How about the CSE formula
=INDEX(B:B,MAX(($A$1:$A$100=A1)*ROW($A$1:$A$100)),1)-VLOOKUP(A1,A:A,B:B,False)

 

[gregtx81]

What does the "ByVal" declaration of the function do?

Don't fully understand the particulars, but it's absence will at times cause an error.

It means, if you change the passed argument within the function, it won't affect that argument's status/value within the calling procedure.