How to write a conditional statement where something occurs twice.

**RavaginRadish** · 12-06-2020, 06:20 PM

I am trying to create a function that runs through multiple values and determines where the value in Column A is less than the value in Column B.
This instance has to occur twice within a range of 10 cells (IE: A2:B12), with the range of values exists from A2:B300.

This can happen immediately following one another (A2<B2, A3<B3), or it can happen infrequently (A5<B5, A12<B12).

If this happens twice, my then statement will return the difference between the the cell value of column A in the FIRST instance, and the cell value of column B in the SECOND instance.

IE:
IF
A3<B3
A7<B7

THEN

"A3- B7"

Sorry if this is confusing. English isnt my first language lol

**Ochimus** · 12-09-2020, 07:55 PM

Does what you want.

Finds first record where A<B and sets "a" as Col A value
When next row found where A<B, Col C shows "a-Col B value",
Resets "a" as new base value, and deducts that from third instance, then when next found, and so on.

Option Explicit
Dim a As Long, f As Long, j As Long, m As Long

Sub DIFFERENCES()

'Find last row with data

    With Sheet1
    f = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    For m = 2 To f
    
    
'If COl A  < Col B
        If .Cells(m, 1).Value < .Cells(m, 2).Value Then
            j = j + 1
            
'If this is first instance, copy Col A value
            If j = 1 Then
            a = .Cells(m, 1).Value
            
'If this is second instance, Col C is Col A - Col B
            ElseIf j = 2 Then
            .Cells(m, 3).Value = a - .Cells(m, 2).Value
            
'Reset as first value in next pair
            j = 1
            a = .Cells(m, 1).Value
            
            End If
        End If
    
'Find next record
    Next
    
    End With
        


End Sub

Hope this helps

Ochimus