0

Working on an automatic macro that needs to print out a value if the input data matches certain criteria. The problem I am running into is it gets stuck in a infinite loop and wont go to the next line. I am new to doing loops so any advise or help is welcome. If I try to exit the for it tells me it can't identify the if statement for the next else if.

Update: I have cleaned up the code per suggestions. I added in the .end(x1down) but i get an application-defined or object-defined error and when i use .end(x1down).row I get for each may only iterate over a collection object or an array

I cannot post images yet so i uploaded them to imgur here is the link this shows example of input data and chart data is pulled from https://i.stack.imgur.com/pJxUB.jpg

Sub MonthCalc()

    Dim seat As Range
    Dim cfdp As Integer
    Dim si As Range
    Dim eqp As Range
    Dim leg As Range
    Dim result As String


    For Each seat In Range("e2").End(x1down).Row
        If seat.Value = 2 Then
            For Each si In Range("c2").End(x1down)
                If si.Value >= 0 And si.Value <= 359 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b5").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c5").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d5").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e5").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f5").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g5").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h5").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 400 And si.Value <= 459 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b6").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c6").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d6").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e6").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f6").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g6").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h6").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 500 And si.Value <= 559 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b7").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c7").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d7").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e7").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f7").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g7").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h7").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 600 And si.Value <= 659 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b8").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c8").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d8").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e8").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f8").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g8").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h8").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 700 And si.Value <= 1159 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b9").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c9").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d9").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e9").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f9").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g9").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h9").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 1200 And si.Value <= 1259 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b10").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c10").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d10").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e10").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f10").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g10").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h10").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 1300 And si.Value <= 1659 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b11").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c11").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d11").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e11").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f11").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g11").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h11").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 1700 And si.Value <= 2159 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b12").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c12").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d12").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e12").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f12").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g12").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h12").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 2200 And si.Value <= 2259 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = " 1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b13").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c13").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d13").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e13").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f13").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g13").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h13").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg

                ElseIf si.Value >= 2300 And si.Value <= 2359 Then
                    For Each leg In Range("f2").End(x1down)
                        If leg.Value = "1" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b14").Value
                        ElseIf leg.Value = "2" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c14").Value
                        ElseIf leg.Value = "3" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d14").Value
                        ElseIf leg.Value = "4" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e14").Value
                        ElseIf leg.Value = "5" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("f14").Value
                        ElseIf leg.Value = "6" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("g14").Value
                        ElseIf leg.Value = "7" Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("h14").Value
                        End If
                        Range("g:g") = cfdp
                    Next leg
                End If
            Next si

        ElseIf seat.Value = 3 Then
            For Each eqp In Range("b:b")
                If eqp.Value = 330 Or eqp.Value = 767 Then
                    For Each si In Range("c2").End(x1down)
                        If si.Value >= 0 And si.Value <= 559 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d18").Value
                        ElseIf si.Value >= 600 And si.Value <= 659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d19").Value
                        ElseIf si.Value >= 700 And si.Value <= 1259 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d20").Value
                        ElseIf si.Value >= 1300 And si.Value <= 1659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d21").Value
                        ElseIf si.Value >= 1700 And si.Value <= 2359 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("d22").Value
                        End If
                        Range("g:g") = cfdp
                    Next si

                ElseIf eqp.Value = 777 Or eqp.Value = 787 Then
                    For Each si In Range("c2").End(x1down)
                        If si.Value >= 0 And si.Value <= 559 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b18").Value
                        ElseIf si.Value >= 600 And si.Value <= 659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b19").Value
                        ElseIf si.Value >= 700 And si.Value <= 1259 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b20").Value
                        ElseIf si.Value >= 1300 And si.Value <= 1659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b21").Value
                        ElseIf si.Value >= 1700 And si.Value <= 2359 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("b22").Value
                        End If
                        Range("g:g") = cfdp
                    Next si
                End If

            Next eqp
        ElseIf seat.Value = 4 Then
            For Each eqp In Range("b:b")
                If eqp.Value = 330 Or eqp.Value = 767 Then
                    For Each si In Range("c2").End(x1down)
                        If si.Value >= 0 And si.Value <= 559 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e18").Value
                        ElseIf si.Value >= 600 And si.Value <= 659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e19").Value
                        ElseIf si.Value >= 700 And si.Value <= 1259 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e20").Value
                        ElseIf si.Value >= 1300 And si.Value <= 1659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e21").Value
                        ElseIf si.Value >= 1700 And si.Value <= 2359 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("e22").Value
                        End If
                    Next si

                ElseIf eqp.Value = 777 Or eqp.Value = 787 Then
                    For Each si In Range("c2").End(x1down)
                        If si.Value >= 0 And si.Value <= 559 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c18").Value
                        ElseIf si.Value >= 600 And si.Value <= 659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c19").Value
                        ElseIf si.Value >= 700 And si.Value <= 1259 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c20").Value
                        ElseIf si.Value >= 1300 And si.Value <= 1659 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c21").Value
                        ElseIf si.Value >= 1700 And si.Value <= 2359 Then
                            cfdp = ThisWorkbook.Sheets("FAR117Chart").Range("c22").Value
                        End If
                        Range("g:g") = cfdp
                    Next si

                End If
            Next eqp
        End If
    Next seat
End Sub
Robert Lewis
  • 3
  • 3
  • 2
    You want one loop here probably. Also, don't loop through the entire column. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last cell. That many levels of indentation (and loops) is a code smell. – BigBen Jan 27 '20 at 23:35
  • Can you send your workbook? Debugging is much easier when you have the workbook to hand. – Ross Symonds Jan 28 '20 at 00:01
  • 1
    It would help a lot to explain in words what you want to do with your data. Likely this code could be a lot shorter. – Tim Williams Jan 28 '20 at 00:03
  • @RossSymonds trying to find a way to post the excel file but unable to from work – Robert Lewis Jan 28 '20 at 00:59
  • `ElseIf si.Value >= 400 Then: If si.Value <= 459 Then` - why is this not just `ElseIf si.Value >= 400 And si.Value < 460 Then` - far less unnecessary indenting, moderately easier to read. You may also want to read up on [`Select Case` statements](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement) – Chronocidal Jan 28 '20 at 01:07
  • @TimWilliams this is running a check on data that will range up to 25000 lines plus. Each line has a different indicators separate by eqp, seat, leg and si. If the input data is within a certain range of numbers it will print out the appropriate response which is in a separate table. – Robert Lewis Jan 28 '20 at 01:08
  • Are all of the criteria in the same line on the sheet though ?- you have multiple nested loops each of which is checking every row – Tim Williams Jan 28 '20 at 01:23
  • @TimWilliams yes the data is in the same lines throughout the sheet just the values differ – Robert Lewis Jan 28 '20 at 01:32
  • This line `Range("g:g") = cfdp` fills the whole column with a value. Is that the intention? – Ricardo Diaz Jan 28 '20 at 01:57
  • It's `xlDown` - the letter `l` as in `Lima`, not the number `1`. – BigBen Jan 28 '20 at 01:58
  • @BigBen kk fixing it now tired eyes easy mistake lol – Robert Lewis Jan 28 '20 at 02:02
  • correct all the 1 to l but still getting application-defined or object-defined error – Robert Lewis Jan 28 '20 at 02:08
  • Here are images of sample data and chart numbers is pulled from https://imgur.com/a/3wiIX1t – Robert Lewis Jan 28 '20 at 02:58

2 Answers2

1

Another alternative combining selects and if statements:

Also untested. If you post some sample data would be easier to debug.

Either way, you can press F8 and step through the code to find out if the logic is working.

EDIT: Adjusted cases when seats = 3 (seats = 4 please check how I code the previous cases)

Public Sub MonthCalc()

    ' Define objects
    Dim sourceSheet As Worksheet
    Dim chartSheet As Worksheet
    Dim evalCell As Range

    ' Define other variables
    Dim lastRow As Long
    Dim chartColumn As Long
    Dim chartRow As Long

    Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
    Set chartSheet = ThisWorkbook.Sheets("FAR117Chart")

    lastRow = sourceSheet.Range("E2").End(xlDown).Row

    For Each evalCell In Range("E2:E" & lastRow).Cells


        If evalCell.Value = 2 Then

            ' Get the offset column in column F
            chartColumn = evalCell.Offset(0, 1).Value - 1

            Select Case evalCell.Offset(0, -2).Value
            ' Column e = 2, c >= 0, c <= 359
            Case 0 To 359
                chartRow = 5
            ' Column e = 2, c >= 400, c <=459
            Case 400 To 459
                chartRow = 6
            ' Column e = 2, c >= 500, c <=559
            Case 500 To 559
                chartRow = 7
            ' Column e = 2, c >= 600, c <=659
            Case 600 To 659
                chartRow = 8
            ' Column e = 2, c >= 700, c <=1159
            Case 700 To 1159
                chartRow = 9
            ' Column e = 2, c >= 1200, c <=1259
            Case 1200 To 1259
                chartRow = 10
            ' Column e = 2, c >= 1300, c <=1659
            Case 1300 To 1659
                chartRow = 11
            ' Column e = 2, c >= 1700, c <=2159
            Case 1700 To 2159
                chartRow = 12
            ' Column e = 2, c >= 2200, c <=2259
            Case 2200 To 2259
                chartRow = 13
            ' Column e = 2, c >= 2300, c <=2359
            Case 2300 To 2359
                chartRow = 14
            End Select

        ElseIf evalCell.Value = 3 Then

            ' Column e = 3, b >= 330, b <=767
            If evalCell.Offset(0, -3).Value = 330 Or evalCell.Offset(0, -3).Value = 767 Then

                ' Get the offset column in column D
                chartColumn = 2

                Select Case evalCell.Offset(0, -2).Value
                Case 0 To 559
                    chartRow = 18
                Case 600 To 659
                    chartRow = 19
                Case 700 To 1259
                    chartRow = 20
                Case 1300 To 1659
                    chartRow = 21
                Case 1700 To 2359
                    chartRow = 22
                End Select

            ' Column e = 3, b >= 777, b <=787
            ElseIf evalCell.Offset(0, -3).Value = 777 Or evalCell.Offset(0, -3).Value = 787 Then

                ' Get the offset column in column D
                chartColumn = 0

                Select Case evalCell.Offset(0, -2).Value
                Case 0 To 559
                    chartRow = 18
                Case 600 To 659
                    chartRow = 19
                Case 700 To 1259
                    chartRow = 20
                Case 1300 To 1659
                    chartRow = 21
                Case 1700 To 2359
                    chartRow = 22
                End Select

            End If

        ElseIf evalCell.Value = 4 Then

            ' Add conditionals

        End If

        'Cell G in the same line
        evalCell.Offset(0, 2).Value = chartSheet.Range("B5").Offset(chartRow, chartColumn).Value

    Next evalCell

End Sub

Let me know if it helps.

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • this works except when it checks the seats 3 and 4 nothing prints out I have posted the data being used and the data of the chart onto imgur link https://imgur.com/a/3wiIX1t Thank you for all the help so far – Robert Lewis Jan 28 '20 at 12:59
0

EDIT: since your data is on a worksheet you don't need to encode the range values in your VBA: just lookup the data directly from your table. Here's an example based on your screenshots:

'check a time value against the first column of a table where
'  values are of the form xxxx-xxxx: when matched return
'  the value from column number colNum
Function TimeLookup(tm, rng As Range, colNum As Long)
    Dim rw As Range, data, arr, r As Long, v, rv, ok As Boolean
    data = rng.Value
    For r = 1 To UBound(data, 1)
        v = data(r, 1)
        If InStr(v, "-") > 0 Then
            arr = Split(v, "-")
            If tm >= CLng(arr(0)) And tm <= CLng(arr(1)) Then
                rv = data(r, colNum)
                ok = True
                Exit For
            End If
        End If
    Next r
    'return the found value of #N/A if not match
    TimeLookup = IIf(ok, rv, CVErr(xlErrNA))
End Function

Here's an untested and incomplete suggestion as to how you could handle this:

Sub MonthCalc()

    Dim ws As Worksheet, rw As Range, iblank As Long

    Set ws = ActiveSheet

    Set rw = ws.Range("A2:J2") ' start on (eg) row 2 of your data

    'keep processing until we've had (eg) 10 consecutive empty rows
    Do While iblank < 10

        If Application.CountA(rw) > 0 Then

            ProcessRow rw 'process this row

            iblank = 0 'reset blank count
        Else
            iblank = iblank + 1 'increment blank count
        End If

        Set rw = rw.Offset(1, 0) 'next row
    Loop

End Sub

Sub ProcessRow(rw As Range)
    Dim wsFar As Worksheet
    Dim seat, si, leg, eqp, rw As Long

    Set wsFar = ThisWorkbook.Worksheets("FAR117Chart")

    eqp = rw.Cells(1, "B").Value
    seat = rw.Cells(1, "E").Value
    si = rw.Cells(1, "C").Value
    leg = rw.Cells(1, "F").Value

    'check we have numeric values
    If OKnumber(seat) And OKnumber(si) And OKnumber(leg) Then
        If leg >= 1 And leg <= 7 Then
            'case where seat=2
            If seat = 2 Then
                rw = 0
                Select Case True
                    Case (si >= 0 And si <= 359): rw = 5
                    Case (si >= 400 And si <= 459): rw = 6
                    Case (si >= 500 And si <= 559): rw = 7
                    Case (si >= 600 And si <= 659): rw = 8
                    Case (si >= 700 And si <= 1159): rw = 9
                    Case (si >= 1200 And si <= 1259): rw = 10
                    Case (si >= 1300 And si <= 1659): rw = 11
                    Case (si >= 1700 And si <= 2159): rw = 12
                    Case (si >= 2200 And si <= 2259): rw = 13
                    Case (si >= 2300 And si <= 2359): rw = 14
                End Select

                If rw <> 0 Then
                    rw.Cells(1, "G").Value = wsFar.Cells(rw, 1).Offset(0, leg).Value
                End If
            End If

            'handle other seat values....


        End If
    End If
End Sub

'check if a value is no-blank and numeric: cast to long if yes
Function OKnumber(ByRef v) As Boolean
    Dim rv As Boolean
    rv = Len(v) > 0 And IsNumeric(v)
    If rv Then v = CLng(v) 'convert argument to long if numeric
    OKnumber = rv
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125