Clicking a VBA Macro button always brings up the "Go To" (hotkey F5) menu after running.

Sub LoopDetect(RouteName, LoopTest, LoopXTest, StopDetect)  'Detects if a route is a LOOP or LOOPX route. If so, truncates the route name.

Dim test As String


If StopDetect = 0 Then

    test = Right(RouteName, 4)
    LoopTest = 0
    LoopXTest = 0

        If test = "LOOP" Then
        LoopTest = 1
''        Debug.Print "This is a LOOP Move"
        End If

    test = Right(RouteName, 5)

        If test = "LOOP2" Then
        LoopXTest = 1
''        Debug.Print "This is a LOOPX Move"
            Else
            If test = "LOOP3" Then
            LoopXTest = 1
''            Debug.Print "This is a LOOPX Move"
                Else
                If test = "LOOP4" Then
                LoopXTest = 1
''                Debug.Print "This is a LOOPX Move"
               End If
            End If
        End If
    Else
''    Debug.Print "No LOOP Detection Required"
    End If

End Sub

Sub RouteReturn(RouteName, HomeSig, ExitSig, LoopTest, LoopXTest, StopDetect)

Dim test As String
Dim dash As Integer

If StopDetect = 0 Then
    If LoopTest = 1 Then
    RouteName = Left(RouteName, Len(RouteName) - 5)
        Else
        If LoopXTest = 1 Then
        RouteName = Left(RouteName, Len(RouteName) - 6)
        End If
    End If
''Debug.Print "Modified Route Name is " & RouteName

dash = InStr(RouteName, "-")

HomeSig = Left(RouteName, dash - 1)
''Debug.Print "Home Signal is " & HomeSig

ExitSig = Mid(RouteName, dash + 1)
''Debug.Print "Exit Signal is " & ExitSig

End If

End Sub

Sub SignalParse(LineNum, IXLName, SigName, ChainVal)

    'This subroutine performs a singular MATCH to locate the range of the Interlocking by itself.
    'It then searches within this smaller range to find chaining of the specific signal value.

Dim ActiveSht As Worksheet


Dim FirstRow_Sig As Long                            'First row number for in-context Interlocking data
Dim LastRow_Sig As Long                             'Last row number for in-context Interlocking data
Dim RowCount_Sig As Integer                         'Total number of rows containing Interlocking data

Dim RowVar_Sig As Integer: RowVar_Sig = 3           'Row 3 is the first row of Route Data.
Dim RowOff_Sig As Integer: RowOff_Sig = 3           'Due to this, local range reference using index match is off by 3.
Dim DataRow_Sig As Integer                          'Incremental row variable for chaining search

'Dim LookupRange As Range
Dim IXL_Range_Sig As String
Dim IXL_ColLet_Sig As String
Dim NameColNum_Sig As Integer

Dim ValFound As Boolean


Set ActiveSht = ThisWorkbook.Worksheets("Line" & LineNum)
ActiveSht.Select

''Debug.Print "Selected sheet is " & ActiveSht.Name

'IXL_ColLet_Sig = Replace(ActiveSht.Cells(1, [L4_Sig_Location].Column).Address(0, 0), 1, "")
IXL_ColLet_Sig = "A"

''Debug.Print "Interlocking column letter is " & IXL_ColLet_Sig

FirstRow_Sig = (Application.WorksheetFunction.Match(IXLName, [L4_Sig_Location], 0) + RowOff_Sig)
RowCount_Sig = Application.WorksheetFunction.CountIf([L4_Sig_Location], IXLName)
LastRow_Sig = FirstRow_Sig + RowCount_Sig - 1

'Specifies the range data for the particular Interlocking.
IXL_Range_Sig = (IXL_ColLet_Sig & FirstRow_Sig & ":" & Chr(Asc(IXL_ColLet_Sig) + 4) & LastRow_Sig)

'Set LookupRange = Range(IXL_Range_Sig)

''Debug.Print "Interlocking range is " & IXL_Range_Sig

'
'Start row by row search for corresponding chaining information.
'

ValFound = False
DataRow_Sig = FirstRow_Sig
NameColNum_Sig = Range(IXL_ColLet_Sig & "1").Column + 1

''Debug.Print "Look for the chaining value for Signal " & SigName

While ValFound = False

''Debug.Print "Selected name: " & ActiveSht.Cells(DataRow_Sig, NameColNum_Sig)

If ActiveSht.Cells(DataRow_Sig, NameColNum_Sig) = SigName Then
   ChainVal = ActiveSht.Cells(DataRow_Sig, NameColNum_Sig + 3)
''Debug.Print "Home Signal value " & ChainVal & " found in row " & DataRow_Sig
    ValFound = True

    Else
''Debug.Print "Desired entry is not in row " & DataRow_Sig
    DataRow_Sig = DataRow_Sig + 1

   If DataRow_Sig > LastRow_Sig Then
''        Debug.Print "Chaining Value Not Found"
        ChainVal = 0
        ValFound = True

    End If
End If

Wend

  'These chaining values must be rounded before AND after all calculations. The "raw" data may contain values to several
  'decimal places, and averaged odd numbers will return a decimal. Must be integers.

End Sub

Sub SwitchParse(LineNum, IXLName, SwName, SwState, ChainVal_Sw)

    'This subroutine performs a singular MATCH to locate the range of the Interlocking by itself.
    'It then searches within this smaller range to find chaining of the specific switch value.
    'If the switch is paired, it pulls two chainings and averages them.

Dim ActiveSht As Worksheet


Dim FirstRow_Sw As Long
Dim LastRow_Sw As Long
Dim RowCount_Sw As Integer

Dim RowVar_Sw As Integer: RowVar_Sw = 3             'Row 3 is the first row of Route Data.
Dim RowOff_Sw As Integer: RowOff_Sw = 3             'Due to this, local range reference using index match is off by 3.
Dim DataRow_Sw As Integer

Dim LookupRange As Range
Dim IXL_Range_Sw As String
Dim IXL_ColLet_Sw As String
Dim NameColNum_Sw As Integer

Dim SwAChain As Long
Dim SwBChain As Long
Dim ChainVal As Long

Dim ValFound As Boolean
Dim SwitchPaired As Boolean


Set ActiveSht = ThisWorkbook.Worksheets("Line" & LineNum)
ActiveSht.Select

''Debug.Print "Selected sheet is " & ActiveSht.name

'IXL_ColLet_Sw = Replace(ActiveSht.Cells(1, [L4_Sw_Location].Column).Address(0, 0), 1, "")
IXL_ColLet_Sw = "S"

''Debug.Print "Interlocking column letter is " & IXL_ColLet_Sw

FirstRow_Sw = (Application.WorksheetFunction.Match(IXLName, [L4_Sw_Location], 0) + RowOff_Sw)
RowCount_Sw = Application.WorksheetFunction.CountIf([L4_Sw_Location], IXLName)
LastRow_Sw = FirstRow_Sw + RowCount_Sw - 1

'Specifies the range data for the particular Interlocking.
IXL_Range_Sw = (IXL_ColLet_Sw & FirstRow_Sw & ":" & Chr(Asc(IXL_ColLet_Sw) + 4) & LastRow_Sw)

Set LookupRange = Range(IXL_Range_Sw)

''Debug.Print "Interlocking range is " & IXL_Range_Sw

If SwState = "Y" Then
SwitchPaired = True
   Else
   If SwState = "N" Then
   SwitchPaired = False
   End If
End If

'
'Start row by row search for corresponding chaining information.
'

ValFound = False
DataRow_Sw = FirstRow_Sw
NameColNum_Sw = Range(IXL_ColLet_Sw & "1").Column + 1

'THIS CASE IS FOR SINGULAR SWITCHES

If SwitchPaired = False Then

''Debug.Print "Look for the chaining value for switch " & SwName

    While ValFound = False

''  Debug.Print "Selected name: " & ActiveSht.Cells(DataRow_Sw, NameColNum_Sw)

    If ActiveSht.Cells(DataRow_Sw, NameColNum_Sw) = SwName Then
        ChainVal_Sw = ActiveSht.Cells(DataRow_Sw, NameColNum_Sw + 3)
''      Debug.Print "Switch 1 value " & ChainVal_Sw & " found in row " & DataRow_Sw
        ValFound = True

        Else
''      Debug.Print "Desired entry is not in row " & DataRow_Sw
        DataRow_Sw = DataRow_Sw + 1

        If DataRow_Sw > LastRow_Sw Then
''            Debug.Print "Chaining Value Not Found"
            ChainVal_Sw = 0
           ValFound = True

       End If
    End If

   Wend


End If

'THIS CASE IS FOR PAIRED SWITCHES

If SwitchPaired = True Then

Dim SwNameA As String
SwNameA = SwName & "A"

Dim SwNameB As String
SwNameB = SwName & "B"

''Debug.Print "Look for the chaining values for switches " & SwNameA & " and " & SwNameB

    While ValFound = False

''  Debug.Print "Selected name: " & ActiveSht.Cells(DataRow_Sw, NameColNum_Sw)

    If ActiveSht.Cells(DataRow_Sw, NameColNum_Sw) = SwNameA Then
        SwAChain = ActiveSht.Cells(DataRow_Sw, NameColNum_Sw + 3)
''      Debug.Print "Switch 1 value " & SwAChain & " found in row " & DataRow_Sw
       ValFound = True

       Else
''      Debug.Print "Desired entry is not in row " & DataRow_Sw
        DataRow_Sw = DataRow_Sw + 1

       If DataRow_Sw > LastRow_Sw Then
           ''Debug.Print "Chaining Value Not Found"
           SwAChain = 0
          ValFound = True

       End If
   End If

   Wend

ValFound = False                    'Reinitialize the row by row search
DataRow_Sw = FirstRow_Sw

    While ValFound = False

''Debug.Print "Selected name: " & ActiveSht.Cells(DataRow_Sw, NameColNum_Sw)

    If ActiveSht.Cells(DataRow_Sw, NameColNum_Sw) = SwNameB Then
        SwBChain = Cells(DataRow_Sw, NameColNum_Sw + 3)
''      Debug.Print "Switch 2 value " & SwBChain & " found in row " & DataRow_Sw
        ValFound = True

       Else
''      Debug.Print "Desired entry is not in row " & DataRow_Sw
        DataRow_Sw = DataRow_Sw + 1

        If DataRow_Sw > LastRow_Sw Then
''          Debug.Print "Chaining Value Not Found"
            SwBChain = 0
            ValFound = True

        End If
    End If

    Wend

    ChainVal_Sw = 0.5 * (SwAChain + SwBChain)
''Debug.Print "Modified chaining value is " & ChainVal_Sw

End If


End Sub
/r/vba Thread Parent