Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
endless loop help
I want to copy values on one page and paste them onto another. Sometimes my
find should find only one entry, other times it should find a few... either way I am stuck since I added the loop command. Not real familiar with how the DO and LOOP commands work. here is the code... can someone tell me why I am in an endless loop Application.ScreenUpdating = False ActiveCell.Copy Range("z1").Select ActiveCell.PasteSpecial xlPasteValues Range("j10:m10").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("a1").Select Dim rngToSearch As Range Dim wks As Worksheet Dim rngFound As Range Dim WhatToFind As Variant Sheets("Contacts").Select Set wks = Sheets("Assignments") Set rngToSearch = Sheets("contacts").Columns(1) 'Set WhatToFind = wks.Range("z1").Value On Error Resume Next Set rngFound = rngToSearch.find(what:=wks.Range("z1").Value, LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then MsgBox "error" Else Do rngFound.Resize(1, 4).Copy wks.Select Range("j9").Select 'Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial xlPasteValues Loop Until rngFound.Value < rngFound.Offset(1, 0).Value 'If rngFound.Value = rngFound.Offset(1, 0) Then Set rngFound = rngToSearch.FindNext 'Else 'GoTo ender: 'End If End If ender: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
endless loop help
John wrote:
I want to copy values on one page and paste them onto another. Sometimes my find should find only one entry, other times it should find a few... either way I am stuck since I added the loop command. Not real familiar with how the DO and LOOP commands work. here is the code... can someone tell me why I am in an endless loop On Error Resume Next Set rngFound = rngToSearch.find(what:=wks.Range("z1").Value, LookIn:=xlValues, lookat:=xlWhole) If rngFound Is Nothing Then MsgBox "error" Else Do rngFound.Resize(1, 4).Copy wks.Select Range("j9").Select 'Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial xlPasteValues Loop Until rngFound.Value < rngFound.Offset(1, 0).Value 'If rngFound.Value = rngFound.Offset(1, 0) Then Set rngFound = rngToSearch.FindNext 'Else 'GoTo ender: 'End If John: The Find method keeps finding as long as there is something to find. That is, it loops back to the first found cell. You need to save the address of the first found cell, then include a test in your Loop statement to see when you get back there. See here http://www.dicks-blog.com/archives/2...e-find-method/ -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file won't open, endless loop, autorecover | Excel Discussion (Misc queries) | |||
Endless loop? | Excel Programming | |||
deleting rows, endless loop maybe ? | Excel Programming | |||
Interrupting an endless loop | Excel Programming | |||
Endless Loop when using ComboBox1.BoundColumn = 2 | Excel Programming |