0

I am having some trouble with the grabbing of values from a chart it is grabbing the wrong values and if seats = 3 or 4 prints out nothing. I get no error when the code runs and I am changing column values but running in circles trying to figure out why it is not pulling the correct values.

The code is checking the values from input data seen below (sample input data image) and running the select cases to determine which value the input data matches for specific criteria then outputs a value from a specific cell in the chart.

The problem I am having now is that the values are not printing correctly for example:

Input data line 2 Eqp 767, Si 0400, Seat 2, Legs 2. So if Seat = 2 then it checks for the number of legs, then checks where the Si values fall into a range of time 0400-0459.

            If evalCell.Value = 2 Then
            Select Case evalCell.Offset(0, 1).Value

            Case 1
            ' Get the offset column in column F
            chartColumn = b

            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

The output should be 1400 which is in cell C6 on the chart but it outputs 1600.

Chart Data https://i.imgur.com/4hREPN8.jpg

Table C is used if seat is 3 or 4. Then breaks down to Columns B & C are 777 and 767, D & E 330 and 787, and G & F are not used by this macro.

Sample Input Data https://i.imgur.com/0mcnVRg.jpg

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("FLT Data")
    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

            Select Case evalCell.Offset(0, 1).Value

            Case 1
            ' Get the offset column in column F
            chartColumn = b

            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

            Case 2
            ' 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

            Case 3
            ' 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

            Case 4
            ' 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

            Case 5
            ' 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

            Case 6
            ' 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

            Case 7
            ' 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
            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 True
                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 True
                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
                 ' 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 True
                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 True
                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

        End If

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

    Next evalCell

End Sub
Robert Lewis
  • 3
  • 3
  • Unrelated to your question, but I have a suggestion that I think will help you debug. Put your `Select Case` statments into a function that returns the `chartRow` based on `Case`. Also, as far as `Select Case True` goes, [see this.](https://stackoverflow.com/questions/794036/select-case-true) – jclasley Jan 28 '20 at 16:27
  • 2
    I recommend to go through the code step by step (using F8) to check the values of your variables and figure out where exactly the first thing goes wrong. Actually your code is too long to figure out where the issue is just by reading it, so you should do some debugging to narrow it down to some point in the code. • Also your question is pretty unclear, I'm not even close to understand what exactly you are trying to do. – Pᴇʜ Jan 28 '20 at 16:29
  • The code is checking the values from input data and running the select cases if it matches a specific case it outputs a value from a specific cell in the chart. The problem I am having now is that the values are not printing correctly for example, input data line 2 is EQP 767, SI 0400, Seat 2, Legs 2. So if seat = 2 then it checks for leg =, then si time in between case values, the output should be 1400 which is c6 on the chart but it outputs 1600. – Robert Lewis Jan 28 '20 at 16:46
  • Sorry but you must be much more specific: What does *"For the chart only seats 3 and 4 will pull from table c. Columns B & C are 777 and 767, D & E 330 and 787"* mean? I can't even see these values in your table. Please [edit] your question, I think comments are too short to expain it. • Try to give one full example: Which cell value are you going to lookup/check where in the table? Try using the cell addresses when explaining or mark something with color. I think otherwise we are lost. Imagine that we have no idea at all about what SEATS and Legs are when you are explaining. – Pᴇʜ Jan 28 '20 at 17:17
  • I have adjusted the original post hopefully it will be more clear – Robert Lewis Jan 28 '20 at 18:15
  • *"then it checks for the number of legs"* what does that mean? Is row 4 the numbers 1-7 the number of legs of column F in the input data table? • Actually I think this could be done with formulas would that be an option for you? – Pᴇʜ Jan 29 '20 at 07:00

0 Answers0