Loop Ends After First Match

[Riddlemethis]

Script below:

Dim rng As Range
Set rng = Range("BJ5:BJ500")

Sheets("Daily Runboard").Activate
For Each cell In rng
If cell.Value = "MCC" Then
cell.Select
ActiveCell.Offset(0, -59).Select
ActiveCell.Resize(11, 22).Copy
Sheets("H Runs").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.PasteSpecial xlPasteFormats
End If
Next

End Sub

So works perfectly at the first cell.value match in the range where it selects the cell, offsets where i need it too, changes range size, copy's, move sheets and pastes correctly ( text and format) too the next relevant empty cell in the sheet i need it too. Problem being it seems to circle the loop but not go down the range correctly after the first cycle where it works OK.. After the first match it seems to cycle through where it no longer meets the correct cell.value but yet it should?
In this specific data dump the next cells down is blank in the range all the way down to where the next cell in BJ5:BJ500 with a vlaue also contains the correct cell.value "MCC" but it passes through the loop when I step by step it as in it doesn't reference the other cells in the rng

What am i doing wrong?

 

[StephenCrump]

On your first match, your code selects Sheets("H Runs").

On the second match, your code tries: cell.Select

But cell is on Sheets("Daily Runboard"), and you need to activate the sheet before you can select a cell on that sheet. (You have Sheets("Daily Runboard").Activate before you start the loop).

If your code isn't erroring, then presumably you have an On Error Resume Next somewhere above? It's bad coding practice to do this.

But in any event, you can get rid of all the .Activate and .Select. It's inefficient, and prone to error relying on which sheet/cell is Active.

Sub Test()

    Dim r As Range
    Dim LastRow As Long
        
    LastRow = Sheets("H Runs").Range("A" & Rows.Count).End(xlUp).Row
    
    For Each r In Sheets("Daily Runboard").Range("BJ5:BJ500")
        If r.Value = "MCC" Then
            r.Offset(0, -59).Resize(11, 22).Copy
            With Sheets("H Runs").Range("A" & LastRow + 1)
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
            End With
            LastRow = LastRow + 11
        End If
    Next r

End Sub

 

[Riddlemethis]

Hi @StephenCrump thanks for your help here, it is appreciated.

So I tried your code which works fine as long as my first value = "MCC" is in the first cell in the range aka "BJ5". It then however moves to Next r and cycles through but doesn't find any other matches in the range even though they exist? Also won't find any matches if the value in "BJ5" is not "MCC".

Any ideas on what I'm doing wrong?

 

[StephenCrump]

I've tested using:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1
2
3
4
5
6
7
8
9			Alpha1																					Omega1																																						MCC
10			Alpha1																					Omega1
11			Alpha1																					Omega1
12			Alpha1																					Omega1
13			Alpha1																					Omega1
14			Alpha1																					Omega1
15			Alpha1																					Omega1
16			Alpha1																					Omega1
17			Alpha1																					Omega1
18			Alpha1																					Omega1
19			Alpha1																					Omega1
20
21
22
23
24			Alpha2																					Omega2																																						MCC
25			Alpha2																					Omega2
26			Alpha2																					Omega2
27			Alpha2																					Omega2
28			Alpha2																					Omega2
29			Alpha2																					Omega2
30			Alpha2																					Omega2
31			Alpha2																					Omega2
32			Alpha2																					Omega2
33			Alpha2																					Omega2
34			Alpha2																					Omega2
35
Daily Runboard

Before running code:

	A	B
1	Blah
2	Blah
3	Blah
4	Blah
5	Blah
6	Blah
7
8
H Runs

After running code:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Blah
2	Blah
3	Blah
4	Blah
5	Blah
6	Blah
7	Alpha1																					Omega1
8	Alpha1																					Omega1
9	Alpha1																					Omega1
10	Alpha1																					Omega1
11	Alpha1																					Omega1
12	Alpha1																					Omega1
13	Alpha1																					Omega1
14	Alpha1																					Omega1
15	Alpha1																					Omega1
16	Alpha1																					Omega1
17	Alpha1																					Omega1
18	Alpha2																					Omega2
19	Alpha2																					Omega2
20	Alpha2																					Omega2
21	Alpha2																					Omega2
22	Alpha2																					Omega2
23	Alpha2																					Omega2
24	Alpha2																					Omega2
25	Alpha2																					Omega2
26	Alpha2																					Omega2
27	Alpha2																					Omega2
28	Alpha2																					Omega2
29
H Runs

which I think is what you intended?

Note that this line is an exact match, and that the VBA string comparison is case sensitive:

If r.Value = "MCC" Then

You could allow for variations such as "mcc" or "Mcc " by testing:

If UCase(Trim(r.Value)) = "MCC" Then

 

[Riddlemethis]

ahh ok, apologies i probably should of mentioned this or not been lazy and given sample data. So I think where it's going wrong is that there are gaps in between the cells in column "BJ:BJ" so it seems to end when it reaches a blank cell.
I tested your code again manually changing the data dump to have no blanks and it works so my bad. Is there anyway to change it to check through the full range? Or if it helps at all the only cell entry filled out would be exactly 11 cells below the one above starting at "BJ5"

 

[StephenCrump]

So I think where it's going wrong is that there are gaps in between the cells in column "BJ:BJ" so it seems to end when it reaches a blank cell.

No, that's not the problem. My sample data also has gaps in BJ:BJ. The code checks every cell in the range BJ5:BJ500, blank or not.

Or if it helps at all the only cell entry filled out would be exactly 11 cells below the one above starting at "BJ5"

I did wonder whether your data was regular. In this case it will make sense to step through the rows in increments of 11, i.e. testing BJ5, BJ16, BJ27 .. etc.

But first, let's try to understand why the existing code isn't working for you. Is my sample working correctly? What's different about your data?

 

[Riddlemethis]

Actual example range attached. So i've tested it originally assuming value of the cell not actually being "MCC" and being value of left C5 was going to be the problem by changing the cell value to "MCC" however either way of actual text or 'left c5' only works if the first value in the range specified is "MCC". It does however work either way but won't search any further down the range after that.

DRB Test.xlsm
	BJ
1
2
3
4
5	MOS
6
7
8
9
10
11
12
13
14
15
16	MCC
17
18
19
20
21
22
23
24
25
26
27	FRE
28
29
30
31
32
33
34
35
36
37
38	MCC
39
40
41
42
43
44
45
46
47
48
49	MCC
50
51
52
53
54
55
56
57
58
59
60	FRE
61
62
63
64
65
66
67
68
69
70
71	MCC
72
73
74
75
76
77
78
79
80
81
Daily RunBoard

Cell Formulas
Range	Formula
BJ5,BJ71,BJ60,BJ49,BJ38,BJ27,BJ16	BJ5	=LEFT(C5,3)

 

[StephenCrump]

Using your column BJ (values or formula), my code copies four ranges to Sheets("H Runs"): C16:X26, C38:X48, C49:X59, and C71:X81.

At minimum, you must have values in cells C16, C38, C49 and C71, i.e. given that =Left(C16,3) returns "MCC" in cell BJ16 etc. So at minimum, you should see four "MCC*" copied to column A in Sheets("H Runs")?

Are you saying that for the example you've posted, nothing gets copied to Sheets("H Runs")?