VBA macro to add flag to column, with predefined parameters

[jindon]

NickWels

Optimized the function, resulting in a significant speed increase

Sub test()
    Dim myList, s(2), a, i&, ii&, t&, myRow&, myItem, x&
    myList = Array(Array("Symbol", ""), Array("Item", ""), Array("Flag", ""))
    Application.DisplayAlerts = False
    For i = 0 To 2
        On Error Resume Next
        myList(i)(1) = Application.InputBox("Select column for " & myList(i)(0), Type:=8).Column
        If t < myList(i)(1) Then t = myList(i)(1)
        If myList(i)(1) = "" Then Exit Sub
        On Error GoTo 0
    Next
    s(0) = InputBox("Enter Symbol"): If s(0) = "" Then Exit Sub
    s(1) = Application.InputBox("Enter Value", Type:=1)
    Application.ScreenUpdating = False
    With [a1].CurrentRegion.Resize(, t).Offset(1)
        a = .Value2
        ReDim b(1 To UBound(a, 1), 1 To 1)
        Do
            s(2) = s(2) + s(1)
            If s(2) > UBound(a, 1) Then Exit Do
            x = GetNearestRow(a, s, myList)
            If x Then b(x, 1) = "x"
        Loop
        .Columns(myList(2)(1)).Value = b
    End With
    MsgBox "done"
    Application.ScreenUpdating = True
End Sub


Function GetNearestRow&(a, s, myList)
    Dim i&
    For i = 0 To s(1) - 1
        If s(2) + i <= UBound(a, 1) Then
            If a(s(2) + i, myList(0)(1)) = s(0) Then GetNearestRow = s(2) + i: Exit For
        End If
        If a(s(2) - i, myList(0)(1)) = s(0) Then GetNearestRow = s(2) - i: Exit For
    Next
End Function