Endless VBA Loop Excel

[tjamesp]

Hello,

I am an intermediate VBA user. I have made a marco that will search an excel worksheet for a manually defined text string and paste ALL rows corresponding to cells containing the text string into a new sheet. It works great except that I never stops. I cannot seem to get the loop to end. Please help.

Sub test()
Dim lngNextRow As Long
Dim strMyString As String
Sheets("ILP YTD").Activate
Range("H9").Activate


strMyString = InputBox("Enter the number you wish to find")
Do

Cells.Find(What:=strMyString, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

ActiveCell.EntireRow.Copy

Sheets("Search").Select
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lngNextRow).PasteSpecial


Sheets("ILP YTD").Activate


Loop Until IsEmpty(ActiveCell.Offset(1, 0))


End Sub

 

[SkipVought]

hi,

Put a BREAK in your code and step thru it to OBSERVE what is happening. faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

IsEmpty Function


Returns a Boolean value indicating whether a variable has been initialized.

Syntax

IsEmpty(expression)

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

Remarks

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

It's not entirely clear that IsEmpty is doing what you think it's doing when you feed it ActiveCell.Offset(1, 0).

 

[UnsolvedCoding]

Loop Until IsEmpty(ActiveCell.Offset(1, 0))

Should be

Loop Until IsEmpty(ActiveCell.Offset(1, 0).value)

or

if Activecell.offset(1,0).value = "" then exit do

Loop

Without the .value the loop won't work as expected

 

[UnsolvedCoding]

One other things I just thought of that will come in handy.

Include the trim statment

if trim(activecell.offset(1,0).value) = "" then exit do

Including the trim statment ensures you aren't getting a space from a cell that screws things up.

 

[Burser]

This code comes with the caveat that I haven't tested it, haven't spell checked it, and wrote it in notepad. It may require debugging before it'll work.

This is how I do Find loops.

Public Sub Test()

	Dim sMyString As String: sMyString = InputBox("Enter the number you wish to find")
	Dim rFirst As Range: Set rFirst = Nothing
	Dim rCurrent As Range: Set rCurrent = Nothing

	Dim wsSource As Worksheet: Set wsSource = Sheets("ILP YTD")
	Dim wsDest As Worksheet: Set wsDest = Sheets("Search")


	Set rFirst = wsSourceCells.Find(What:=strMyString, After:=wsSourceCells.Range("H9"), LookIn:=xlFormulas, LookAt _
		        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

	If Not rFirst Is Nothing Then
		Set rCurrent = rFirst
		Do
			Call rCurrent.EntireRow.Copy(wsDest.Rows(wsDest.Rows.Count.End(xlUp).Row + 1).EntireRow)
			Set rCurrent = wsSourceCells.FindNext(rCurrent)
		Loop Until rCurrent.Address(External:=True) = rFirst.Address(External:=True)
	End If
End Sub

And here's a quote from the help file:

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.