View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
merjet merjet is offline
external usenet poster
 
Posts: 812
Default Returning two similar values from two different columns

This might help. Suppose the source of the two lines is Range("B2:C25") on Sheet1.

Sub FindCrossover()
Dim dDiff(2 To 25) As Double
Dim dMin As Double
Dim iCt As Integer
Dim iX As Integer
For iCt = 2 To 25
dDiff(iCt) = Sheet1.Cells(iCt, "B") - Sheet1.Cells(iCt, "C")
Next iCt
For iCt = 3 To 25
If (dDiff(iCt) = 0 And dDiff(iCt - 1) < 0) _
Or (dDiff(iCt) <= 0 And dDiff(iCt - 1) 0) Then
'found crossover
iX = iCt
dMin = Abs(dDiff(iCt))
If Abs(dDiff(iCt - 1)) < Abs(dDiff(iCt)) Then
dMin = dDiff(iCt - 1)
iX = iCt - 1
End If
Debug.Print iX, Sheet1.Cells(iX, "B"), Sheet1.Cells(iX, "C")
Exit For
End If
Next iCt
End Sub