How to end a loop 1

[JwithaL]

I've created many different loops that involve find functions, but I can't figure a way to end them without an error. They macro itself works fine and runs all the way through.

However, because it is moving and replacing data... at the end of the sheet it can't find what it was searching for so it ends with an error.

Is there any way to tell it to end at the end of the data and not search anymore. This way I can run multiple macros in one - Or - there is one cell at the end of the data that contains the word "printed" along with other numbers which I could use to end it. The only problem is that "printed" is not the only content in that cell. If there was a way to see if a cell contains "printed" or *printed this would be easy.

Any help appreciated

 

[PHV]

Have a look at the Like operator or the InStr function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Since I don't know what function(s) you are using it's difficult to give a specific answer. In general, however, you should be able to test for an error condition and exit the loop in that situation. That would be the best way to handle it, but, if you wanted to key off a marker at the end of the data (e.g. "printed"), you could detect that using something like the following:

If Instr(1,Cells(row,column).Text,"printed") > 0 Then
  Exit Loop
End If

Hope this helps
Mike

 

[JwithaL]

How would you put that in a Loop Until function or am I not thinking in the right area

 

[JwithaL]

I'm sorry I probably should have put the code up in the first place here it is

    Do
    Range("A1").Select
    Cells.find(What:="Item Number:", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 3).Range("A1").Select
    Selection.Copy
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(-1, 0).Rows("1:2").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
    Selection.End(xlDown).Select
    If ---- problem area---- the *printed would be at "active.offset(3,0)"
    End
    End If
    Loop

 

[rmikesmith]

Before we get too carried away here, you are using the Find method of the Range object very inefficiently. Take a look at the example in the VBA Help for Find. It will show how to use the Find and FindNext methods to scan an entire range. You will see how the loop is exited when no matching data is found. This will take care of your problem nicely (no need to rely on an artificial data marker).
Also, there is almost never a need to select a cell or range in order to do something with it (e.g. copy the cell contents). This only slows your code down. In this case, when set up properly, the Find method returns a cell (Range) with the matching data. As in the example, you can operate on the assigned variable directly.

Post back if you need additional assistance after looking at the Help.

Mike

 

[JwithaL]

Thank you very much... I'm fairly new to this thing and having one hell of a quick crash course. I'll check out that spot and see if I can't fix the whole thing up.

Thanks again

 

[JwithaL]

only one problem... not finding that "VBA Help for Find

 

[rmikesmith]

Try accessing it through the VBE (Visual Basic Editor). Specifically, activate the Object Browser, scroll down and select the Range object (left pane) then select/right-click on the Find Method (right pane) and choose Help on the pop-up menu.


Mike