# Loop Ends After First Match



## Riddlemethis (Dec 12, 2022)

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 (Dec 12, 2022)

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 (Dec 14, 2022)

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 (Dec 14, 2022)

I've tested using:

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ123456789Alpha1Omega1MCC10Alpha1Omega111Alpha1Omega112Alpha1Omega113Alpha1Omega114Alpha1Omega115Alpha1Omega116Alpha1Omega117Alpha1Omega118Alpha1Omega119Alpha1Omega12021222324Alpha2Omega2MCC25Alpha2Omega226Alpha2Omega227Alpha2Omega228Alpha2Omega229Alpha2Omega230Alpha2Omega231Alpha2Omega232Alpha2Omega233Alpha2Omega234Alpha2Omega235Daily Runboard

*Before running code:*

AB1Blah2Blah3Blah4Blah5Blah6Blah78H Runs

*After running code:*

ABCDEFGHIJKLMNOPQRSTUVW1Blah2Blah3Blah4Blah5Blah6Blah7Alpha1Omega18Alpha1Omega19Alpha1Omega110Alpha1Omega111Alpha1Omega112Alpha1Omega113Alpha1Omega114Alpha1Omega115Alpha1Omega116Alpha1Omega117Alpha1Omega118Alpha2Omega219Alpha2Omega220Alpha2Omega221Alpha2Omega222Alpha2Omega223Alpha2Omega224Alpha2Omega225Alpha2Omega226Alpha2Omega227Alpha2Omega228Alpha2Omega229H 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 (Dec 14, 2022)

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 (Dec 14, 2022)

Riddlemethis said:


> 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.



Riddlemethis said:


> 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 (Dec 14, 2022)

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.xlsmBJ12345MOS678910111213141516MCC1718192021222324252627FRE2829303132333435363738MCC3940414243444546474849MCC5051525354555657585960FRE6162636465666768697071MCC72737475767778798081Daily RunBoardCell FormulasRangeFormulaBJ5,BJ71,BJ60,BJ49,BJ38,BJ27,BJ16BJ5=LEFT(C5,3)


----------



## StephenCrump (Dec 14, 2022)

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")?


----------

