Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OR clause doesn't execute the last condition
I think this "OR" doesn't execute on the last data row becuase the first
condition is also true when it is at the firstdatarow. The test of the < is true. because the firstdatarow doesn't equal the text in the header row but in the case of an OR when it stops the first data row, then in that case it ignores the second part of the OR clause and it doesn't execute for that last time? I need it to read both clauses and execute on that last data row. What I'm trying to do is get the code to execute on the last service group which it skips. If it helps I put the immediate window contents below. I'm getting ready to kick the trash bucket. tia, Public Sub n2m4() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim iRow As Long Dim rowsToAdd As Integer Dim LastRow As Long Dim i As Integer Dim rng As Range Dim SvcGrpNum As Long Dim SvcGrp As String SvcGrpNum = InputBox("Please input the the total number of Service Group connections from the DNP", "Service Group Number", 48) With ActiveWorkbook.Worksheets("VOD") LastRow = .Cells(.Rows.count, ServiceGroupColumn).End(xlUp).Row i = 1 For iRow = LastRow To (FirstDataRow) Step -1 If .Cells(iRow, ServiceGroupColumn).Value < _ .Cells(iRow - 1, ServiceGroupColumn).Value Or _ (iRow = (FirstDataRow - 1)) Then i = i + 1 rowsToAdd = SvcGrpNum - i Set rng = .Cells(iRow, ServiceGroupColumn) SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).EntireRow.Insert rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp i = 1 End If Next iRow End With End Sub ----------------immediate window---------------------- 6 i 103 irow 7 i 102 irow 8 i 101 irow 9 i 100 irow 10 i 99 irow 11 i 98 irow 12 i 97 irow 13 i 96 irow 14 i 95 irow 15 i 94 irow 16 i 93 irow 17 i 92 irow 2 i 91 irow 3 i 90 irow 4 i 89 irow 5 i 88 irow 6 i 87 irow 7 i 86 irow 8 i 85 irow 9 i 84 irow 10 i 83 irow 11 i 82 irow 12 i 81 irow 13 i 80 irow 14 i 79 irow 15 i 78 irow 16 i 77 irow 17 i 76 irow 2 i 75 irow 3 i 74 irow 4 i 73 irow 5 i 72 irow 6 i 71 irow 7 i 70 irow 8 i 69 irow 9 i 68 irow 10 i 67 irow 11 i 66 irow 12 i 65 irow 13 i 64 irow 14 i 63 irow 15 i 62 irow 16 i 61 irow 17 i 60 irow 2 i 59 irow 3 i 58 irow 4 i 57 irow 5 i 56 irow 6 i 55 irow 7 i 54 irow 8 i 53 irow 9 i 52 irow 10 i 51 irow 11 i 50 irow 12 i 49 irow 13 i 48 irow 14 i 47 irow 15 i 46 irow 16 i 45 irow 17 i 44 irow 2 i 43 irow 3 i 42 irow 4 i 41 irow 5 i 40 irow 6 i 39 irow 7 i 38 irow 8 i 37 irow 9 i 36 irow 10 i 35 irow 11 i 34 irow 12 i 33 irow 13 i 32 irow 14 i 31 irow 15 i 30 irow 16 i 29 irow 17 i 28 irow 2 i 27 irow 3 i 26 irow 4 i 25 irow 5 i 24 irow 6 i 23 irow 7 i 22 irow 8 i 21 irow 9 i 20 irow 10 i 19 irow 11 i 18 irow 12 i 17 irow 13 i 16 irow 14 i 15 irow 15 i 14 irow 16 i 13 irow 17 i 12 irow 17 irow 12 i 16 irow 13 i 15 irow 14 i 14 irow 15 i 13 irow 16 i 12 irow |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OR clause doesn't execute the last condition
Maybe try to repeat your testing condition like:
If .Cells(iRow, ServiceGroupColumn).Value < _ .Cells(iRow - 1, ServiceGroupColumn).Value Or _ If .Cells(iRow, ServiceGroupColumn).Value < (iRow = (FirstDataRow - 1)) Then |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OR clause doesn't execute the last condition
Does this help:
If expression1 is And expression2 is Then result is True True True True False True True Null True False True True False False False "Janis" wrote: I think this "OR" doesn't execute on the last data row becuase the first condition is also true when it is at the firstdatarow. The test of the < is true. because the firstdatarow doesn't equal the text in the header row but in the case of an OR when it stops the first data row, then in that case it ignores the second part of the OR clause and it doesn't execute for that last time? I need it to read both clauses and execute on that last data row. What I'm trying to do is get the code to execute on the last service group which it skips. If it helps I put the immediate window contents below. I'm getting ready to kick the trash bucket. tia, Public Sub n2m4() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim iRow As Long Dim rowsToAdd As Integer Dim LastRow As Long Dim i As Integer Dim rng As Range Dim SvcGrpNum As Long Dim SvcGrp As String SvcGrpNum = InputBox("Please input the the total number of Service Group connections from the DNP", "Service Group Number", 48) With ActiveWorkbook.Worksheets("VOD") LastRow = .Cells(.Rows.count, ServiceGroupColumn).End(xlUp).Row i = 1 For iRow = LastRow To (FirstDataRow) Step -1 If .Cells(iRow, ServiceGroupColumn).Value < _ .Cells(iRow - 1, ServiceGroupColumn).Value Or _ (iRow = (FirstDataRow - 1)) Then i = i + 1 rowsToAdd = SvcGrpNum - i Set rng = .Cells(iRow, ServiceGroupColumn) SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).EntireRow.Insert rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp i = 1 End If Next iRow End With End Sub ----------------immediate window---------------------- 6 i 103 irow 7 i 102 irow 8 i 101 irow 9 i 100 irow 10 i 99 irow 11 i 98 irow 12 i 97 irow 13 i 96 irow 14 i 95 irow 15 i 94 irow 16 i 93 irow 17 i 92 irow 2 i 91 irow 3 i 90 irow 4 i 89 irow 5 i 88 irow 6 i 87 irow 7 i 86 irow 8 i 85 irow 9 i 84 irow 10 i 83 irow 11 i 82 irow 12 i 81 irow 13 i 80 irow 14 i 79 irow 15 i 78 irow 16 i 77 irow 17 i 76 irow 2 i 75 irow 3 i 74 irow 4 i 73 irow 5 i 72 irow 6 i 71 irow 7 i 70 irow 8 i 69 irow 9 i 68 irow 10 i 67 irow 11 i 66 irow 12 i 65 irow 13 i 64 irow 14 i 63 irow 15 i 62 irow 16 i 61 irow 17 i 60 irow 2 i 59 irow 3 i 58 irow 4 i 57 irow 5 i 56 irow 6 i 55 irow 7 i 54 irow 8 i 53 irow 9 i 52 irow 10 i 51 irow 11 i 50 irow 12 i 49 irow 13 i 48 irow 14 i 47 irow 15 i 46 irow 16 i 45 irow 17 i 44 irow 2 i 43 irow 3 i 42 irow 4 i 41 irow 5 i 40 irow 6 i 39 irow 7 i 38 irow 8 i 37 irow 9 i 36 irow 10 i 35 irow 11 i 34 irow 12 i 33 irow 13 i 32 irow 14 i 31 irow 15 i 30 irow 16 i 29 irow 17 i 28 irow 2 i 27 irow 3 i 26 irow 4 i 25 irow 5 i 24 irow 6 i 23 irow 7 i 22 irow 8 i 21 irow 9 i 20 irow 10 i 19 irow 11 i 18 irow 12 i 17 irow 13 i 16 irow 14 i 15 irow 15 i 14 irow 16 i 13 irow 17 i 12 irow 17 irow 12 i 16 irow 13 i 15 irow 14 i 14 irow 15 i 13 irow 16 i 12 irow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Clause | Excel Worksheet Functions | |||
Can I use a between clause or in clause on an IF statement | Excel Programming | |||
IF Clause | Excel Programming | |||
"Between" in an IF clause | Excel Discussion (Misc queries) | |||
if then clause in a cell | Excel Programming |