Cells.Find Function not working when used 100x in 1s

[Shuter1]

Hi Everybody,

I am writing a macro that uses data from a spreadsheet with over a 10K rows and around 20 columns. Its exported from Access, as in my opinion it is quicker to use data in a excel sheet as always calling a database.

Anyway to locate the right column and right row i use cells.find function. Its run a few hundred times in a second and it finds nothing even when the data are there. I am sure the code is written correctly because if I step into the macro and go line by line the cells.Find functions find every single piece of data, without problems. It seams to have problem with the count of times I need to use it.

Part of the code:

Please Login or Register  to view this content.

Anyone has experience with this? Do I need to call the Database server for each value/row/column or is there a way how to fix this?

Thank you for any help,
Best Regards,
Shuter

[arlu1201]

Shuter1,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

[HaHoBe]

Hi, Shuter1,

Its run a few hundred times in a second...

Due to the information you deliver it seems to me you import the whole set into Excel but it´s not clear what you looking for and why it has happen so many times.

If Find is really troublesome in your project please try to use WorksheetFunction.Match instead which should deliver the row while Find would give a range.

BTW: why do you use SQLDateFormat instead of just Format, and what are Datum, Data, and Planeta like?

Ciao,
Holger

[Shuter1]

arlu1201: I was thinking it works somehow automatically :-) Sorry, next time I do as you say in your signature.

HaHoBe:
Match did work perfectly. I needed to put the first column values and first row values in to a public array as Match did not want to read it from a Range or Variant type variable. But anyway it works and is still really fast.
Thank you very much, Holger. This thread is solved.

About your questions:
I use a function instead just of format, because my data formats can change and I dont want to read the code searching for all places I changed the format because of the data.
Data is a Worksheet. Datum and Planeta where strings to search for in the Data Worksheet.

My new code:

Please Login or Register  to view this content.