Q:

How to find the max. absolute sequential difference of two values in a given range in VBA

I have got a specific Range e.g B2-I2 (which can vary) that contains values e.g 1,2,4,5,34,4,23,12. The aim is to have a macro which finds the largest absolute difference in that given range when the function is executed. In the above example the largest abs. difference would be  30 (as 34-4). 

A:

try:
Option Explicit

Sub test()

    Dim i As Long, y As Long, ValueArr As Long, ValueY As Long, MaxDiff As Long
    Dim arr As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        arr = Application.Transpose(.Range("B2:I2").Value)

        For i = LBound(arr) To UBound(arr)

            ValueArr = Abs(arr(i, 1))

            For y = 2 To 9

                ValueY = Abs(.Cells(2, y).Value)

                If ValueArr - ValueY > MaxDiff Then
                    MaxDiff = ValueArr - ValueY
                End If

            Next y

        Next i

        MsgBox MaxDiff

    End With

End Sub

