About Us

Valerie

Works like a charm!!! Thank you so much!!

"Per Jessen" wrote:

Hi

This solution exit the loop when 'Find' return to first match found:

Sub test()
Dim fFound As Range
Dim f As Variant
ActiveCell.FormulaR1C1 = "='Master'!R3C9"
ActiveCell.Copy

TotalRowsToDo = ActiveCell.CurrentRegion.Rows.Count
Counter = 1

Set f = Cells.Find(What:="FB01", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If f Is Nothing Then Exit Sub 'No match found

Set fFound = f
Do
f.Offset(0, 6).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Set f = Cells.FindNext(After:=f)
Loop Until f.Address = fFound.Address
End Sub

Regards,
Per

On 26 Apr., 21:30, Valerie wrote:
I am trying to replace cells on certain rows with the value in cell I1 within
column I based on a found row from a cell in column C. I have the following
macro:

ActiveCell.FormulaR1C1 = "='Master'!R3C9"
Selection.Copy

TotalRowsToDo = ActiveCell.CurrentRegion.Rows.Count
Counter = 1

Do Until Counter = TotalRowsToDo
Cells.Find(What:="FB01", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Counter = Counter + 1
Loop

The macro doesn't stop when it gets to the bottom of the range, it just
keeps cycling. I have also tried using IsEmpty
(ActiveCell.Offset(1,0).Select) which doesn't work either.

Please help with solution to stop it when it gets to the bottom of the
populated cells.

Thank you!

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find/Replace Macro; Need Loop	ryguy7272	Excel Programming	7	July 7th 09 04:55 PM
Find/Replace Event or Find/Replace for Protected Sheet ...	Joe HM	Excel Programming	2	October 27th 07 03:55 PM
How to stop Excel from doing wrong Find/Replace	Nekodvoru	Excel Discussion (Misc queries)	3	August 7th 06 06:59 PM
Find & Replace / Loop & Vlookup	thom hoyle	Excel Programming	5	June 25th 05 12:56 AM
HELP!!!! Can't stop a loop (NOT an infinite loop)	TBA[_2_]	Excel Programming	3	December 14th 03 03:33 PM

Menu

About Us