I am trying to copy data from one spreadsheet to another. The code
loops through the first spreadsheet and when a new value is found (I
know it is new using the vlookup worksheet function), a form is shown
for the user to enter some new information (then new spreadsheet
requires more info than the old).

Everything works fine until around the 80th time the form is shown and
then everything slows to a crawl. In debug the bottleneck is the .find
method

With Workbooks(recent).Sheets("Styles").Range("A:A")
Set c = .Find(UCase(Fstyle), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing And Fstyle < "" Then

What is really strange is that if I end the macro and just try a CTRL+F
to search the spreadsheet it takes forever (you can see excel searching
in the name box very slowly). Resaving or closing excel does nothing.
Someone the spreadsheet itself becomes slow. If I try the CTRL+F
before running the macro it works fine.

Any ideas?

When you are done with the form are you hiding it or unloading it? Perhaps
your loop is creating multiple instances of the form, which would eat up
memory & slow down your procedure.

"gloveman" wrote:

I am trying to copy data from one spreadsheet to another. The code
loops through the first spreadsheet and when a new value is found (I
know it is new using the vlookup worksheet function), a form is shown
for the user to enter some new information (then new spreadsheet
requires more info than the old).

Everything works fine until around the 80th time the form is shown and
then everything slows to a crawl. In debug the bottleneck is the .find
method

With Workbooks(recent).Sheets("Styles").Range("A:A")
Set c = .Find(UCase(Fstyle), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing And Fstyle < "" Then

What is really strange is that if I end the macro and just try a CTRL+F
to search the spreadsheet it takes forever (you can see excel searching
in the name box very slowly). Resaving or closing excel does nothing.
Someone the spreadsheet itself becomes slow. If I try the CTRL+F
before running the macro it works fine.

Any ideas?

Try to use MATCH instead of FIND ?

Example:

Worksheets("Styles").Select
data_ran = Range("A1:A15000") ' select your range
On Error Resume Next
irow = WorksheetFunction.Match(Fstyle, data_ran, 1)
If Not Len(Error) 0 Then

End If


"gloveman" wrote:

I am trying to copy data from one spreadsheet to another. The code
loops through the first spreadsheet and when a new value is found (I
know it is new using the vlookup worksheet function), a form is shown
for the user to enter some new information (then new spreadsheet
requires more info than the old).

Everything works fine until around the 80th time the form is shown and
then everything slows to a crawl. In debug the bottleneck is the .find
method

With Workbooks(recent).Sheets("Styles").Range("A:A")
Set c = .Find(UCase(Fstyle), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing And Fstyle < "" Then

What is really strange is that if I end the macro and just try a CTRL+F
to search the spreadsheet it takes forever (you can see excel searching
in the name box very slowly). Resaving or closing excel does nothing.
Someone the spreadsheet itself becomes slow. If I try the CTRL+F
before running the macro it works fine.

Any ideas?

I am unloading it each time, I tried hiding it, but I wanted the
initialize routine to kick off and couldn't get that to happen when
hiding.