Please tell me why this macro is giving a false outcome

[oldbrewer]

It is probably blindingly obvious but I am stumped - logic is explained below

ID	COLOR	TEMP	StartDate	EndDate	Y/N
123ABC	green1	hot	01/01/2017		Y
123ABC			01/01/2017		Y
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC		hot	01/01/2017						macro
123ABC		hot	01/01/2017		Y
123ABC	green3		01/01/2017	31/01/2017					rrow = 39
123ABC			01/01/2017		Y				For k = 1 To 2
123ABC		hot	01/01/2017		Y				Match = Cells(k + 32, 1)
456DEF	green4	hot	08/01/2017	31/01/2017	Y				For j = 2 To 26
456DEF			08/01/2017		Y				If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50
456DEF	green5	hot	08/01/2017	31/01/2017	Y				20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50
456DEF		hot	08/01/2017						40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45
456DEF		hot	08/01/2017		Y				45 rrow = rrow + 1
456DEF	green6		08/01/2017	31/01/2017					For z = 1 To 7
456DEF			08/01/2017		Y				Cells(rrow, z) = Cells(j, z)
456DEF		hot	08/01/2017		Y				Next z
456DEF	green7	hot	08/01/2017		Y				50 Next j
456DEF			08/01/2017		Y				Next k
123ABC	green8	hot	08/01/2017	31/01/2017	Y				End Sub
123ABC		hot	08/01/2017
123ABC		hot	08/01/2017		Y
123ABC	green9		08/01/2017	31/01/2017
123ABC			08/01/2017		Y				the macro is meant to look for all the 123ABC rows (as defined in A33)
123ABC	red1	hot	08/01/2017	31/01/2017	Y				that have no blanks in columns 2 to 6
123ABC			08/01/2017		Y		row 26		and print them from row 40
					col F				and then repeat the search for 456DEF (A34)
									I cannot figure out why the very first row (color = green1)
									appears in the output table
123ABC							row 33
456DEF									I have checked that E2 really is a blank cell by copying another blank cell into it
123ABC	green1	hot	01/01/2017		Y
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC	green8	hot	08/01/2017	31/01/2017	Y
123ABC	red1	hot	08/01/2017	31/01/2017	Y
456DEF	green4	hot	08/01/2017	31/01/2017	Y
456DEF	green5	hot	08/01/2017	31/01/2017	Y
456DEF	green7	hot	08/01/2017		Y

<colgroup><col span="3"><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[shg]

This line

40    If Cells(iRow, 5) <> "" And Cells(iRow, 6) <> "" Then GoTo 45

... doesn't do anything. It goes to line 45 irrespective.

 

[shg]

How about instead,

Sub ob()
  Dim iRowOut       As Long
  Dim cell          As Range
  Dim iRow          As Long
  Dim iCol          As Long

  iRowOut = 39

  For Each cell In Range("A33:A34")
    For iRow = 2 To 26
      If Cells(iRow, 1) = cell.Value Then
        If WorksheetFunction.CountA(Rows(iRow).Range("B1:F1")) = 5 Then
          iRowOut = iRowOut + 1
          Rows(iRow).Range("A1:F1").Copy Cells(iRowOut, "A")
        End If
      End If
    Next iRow
  Next cell
End Sub

	A​	B​	C​	D​	E​	F​
1​	ID​	COLOR​	TEMP​	StartDate​	EndDate​	Y/N​
2​	123ABC	green1​	hot​	1/1/2017​		Y​
3​	123ABC	​	​	1/1/2017​		Y​
4​	123ABC	red2​	hot​	1/1/2017​	31/01/2017​	Y​
5​	123ABC	​	hot​	1/1/2017​		​
6​	123ABC	​	hot​	1/1/2017​		Y​
7​	123ABC	green3​	​	1/1/2017​	31/01/2017​	​
8​	123ABC	​	​	1/1/2017​		Y​
9​	123ABC	​	hot​	1/1/2017​		Y​
10​	456DEF	green4​	hot​	8/1/2017​	31/01/2017​	Y​
11​	456DEF	​	​	8/1/2017​		Y​
12​	456DEF	green5​	hot​	8/1/2017​	31/01/2017​	Y​
13​	456DEF	​	hot​	8/1/2017​		​
14​	456DEF	​	hot​	8/1/2017​		Y​
15​	456DEF	green6​	​	8/1/2017​	31/01/2017​	​
16​	456DEF	​	​	8/1/2017​		Y​
17​	456DEF	​	hot​	8/1/2017​		Y​
18​	456DEF	green7​	hot​	8/1/2017​		Y​
19​	456DEF	​	​	8/1/2017​		Y​
20​	123ABC	green8​	hot​	8/1/2017​	31/01/2017​	Y​
21​	123ABC	​	hot​	8/1/2017​		​
22​	123ABC	​	hot​	8/1/2017​		Y​
23​	123ABC	green9​	​	8/1/2017​	31/01/2017​	​
24​	123ABC	​	​	8/1/2017​		Y​
25​	123ABC	red1​	hot​	8/1/2017​	31/01/2017​	Y​
26​	123ABC	​	​	8/1/2017​		Y​
27​
32​
33​	123ABC
34​	456DEF
35​
40​	123ABC	red2​	hot​	1/1/2017​	31/01/2017​	Y​
41​	123ABC	green8​	hot​	8/1/2017​	31/01/2017​	Y​
42​	123ABC	red1​	hot​	8/1/2017​	31/01/2017​	Y​
43​	456DEF	green4​	hot​	8/1/2017​	31/01/2017​	Y​
44​	456DEF	green5​	hot​	8/1/2017​	31/01/2017​	Y​

 

[oldbrewer]

thanks shg - I like quickbasic approach and it is all I know,,,,