Why Loop only happens once? Baffles Me...
 

I have a macro I have written as follows that will only loop once once when
run via Excel spreadsheet (ctrl-Shift-r). But if I step through the code
the loop will work just fine and run as many times as needed (423 in this
case)

What am I missing here?


i = 3
Do While i < 423 iSearch = Range("D" & i).Value
With Worksheets("Eff&PerData").Range("D1:D" & iEffRows)
Set c = .Find(iSearch, LookIn:=xlValues)
End With
If c Is Nothing Then
Worksheets("EFF_Place_Holders").Select
Range("A" & i & ":J" & i).Select
Selection.Copy
Worksheets("Eff&PerData").Select
Range("A" & iEffRows).Select
ActiveSheet.Paste
iEffRows = iEffRows + 1
'Not Found
Else
Debug.Print ""
'Found
End If
i = i + 1
Loop
End Sub

Why Loop only happens once? Baffles Me...
 

Hi

I'm not sure exactly what you're trying to do with your
code, but I think your problem may be to do with your If
statement.

it should be

If not c is nothing
'then the code for what you want to happen if does find c
else
'what happens if it doesn't find c
end if

-----Original Message-----
I have a macro I have written as follows that will only
loop once once when
run via Excel spreadsheet (ctrl-Shift-r). But if I step
through the code
the loop will work just fine and run as many times as
needed (423 in this
case)

What am I missing here?


i = 3
Do While i < 423 iSearch = Range("D" & i).Value
With Worksheets("Eff&PerData").Range("D1:D" &
iEffRows)
Set c = .Find(iSearch, LookIn:=xlValues)
End With
If c Is Nothing Then
Worksheets("EFF_Place_Holders").Select
Range("A" & i & ":J" & i).Select
Selection.Copy
Worksheets("Eff&PerData").Select
Range("A" & iEffRows).Select
ActiveSheet.Paste
iEffRows = iEffRows + 1
'Not Found
Else
Debug.Print ""
'Found
End If
i = i + 1
Loop
End Sub


.

Why Loop only happens once? Baffles Me...
 

I did make that change to the If statemant.

No Luck. It seems that the loop stops after one iteration when it is
running automatically (F-5, or run from the Excel Worksheet, but if I run it
say manually (f-8) the loop happens just as it is supposed to many times
without stopping.

Rog



"libby" wrote in message
...
Hi

I'm not sure exactly what you're trying to do with your
code, but I think your problem may be to do with your If
statement.

it should be

If not c is nothing
'then the code for what you want to happen if does find c
else
'what happens if it doesn't find c
end if

-----Original Message-----
I have a macro I have written as follows that will only
loop once once when
run via Excel spreadsheet (ctrl-Shift-r). But if I step
through the code
the loop will work just fine and run as many times as
needed (423 in this
case)

What am I missing here?


i = 3
Do While i < 423 iSearch = Range("D" & i).Value
With Worksheets("Eff&PerData").Range("D1:D" &
iEffRows)
Set c = .Find(iSearch, LookIn:=xlValues)
End With
If c Is Nothing Then
Worksheets("EFF_Place_Holders").Select
Range("A" & i & ":J" & i).Select
Selection.Copy
Worksheets("Eff&PerData").Select
Range("A" & iEffRows).Select
ActiveSheet.Paste
iEffRows = iEffRows + 1
'Not Found
Else
Debug.Print ""
'Found
End If
i = i + 1
Loop
End Sub


.