method 'range' of object '_global' failed - find function sometimes fails?

**n1210933** · 07-05-2010, 10:35 AM

Hi,

I'm at the end of my tether with this one...

The code below takes a time history of strain data and filters it so that only the peaks and valleys of the data remain. The data then needs to be re ordered so that it starts with the highest peak/ lowest valley (whichever has the greatest magnitude). What I don't understand is that sometimes the code runs fine and sometimes it breaks at this point.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If you want to download the real deal it's at http://www.dur.ac.uk/p.r.greaves/ and its the 1.9MB file entitled Rainflow_Spreadsheet 2007.xlsm

The problem code is found by clicking on the 'PV Find' Sheet and pressing the 'Peak Valley Slice' button. I've searched the error code and can't find anything similar to what I'm doing... I'm not trained to write code so apologies if it's really obvious where I'm going wrong. I must stress that sometimes this code works fine, regardless of whether it is searching for a max or min value, and I can't find anything that would cause it to stop running.

Any help on this will be greatly appreciated!

**StephenR** · 07-05-2010, 10:58 AM

You need to add code tags as per forum rules.

**n1210933** · 07-06-2010, 04:18 AM

Sorry about that, I've fixed it and highlighted the problem code further up.

**StephenR** · 07-06-2010, 05:53 AM

I can't access your attachment. You'd be better off attaching a slimmed-down version here. Do you know how to debug by stepping through the code with F8? Does minval always have a value?

**n1210933** · 07-06-2010, 05:59 AM

I think I may have solved it by using application.match instead of find. Bit unsatisfactory but if it works I'll live with it!

**romperstomper** · 07-06-2010, 06:09 AM

Whether you use Match or Find, you really ought to check there actually is a match (or a found cell) before you attempt to use it...

**n1210933** · 07-06-2010, 06:17 AM

minval corresponds to the minimum value in a set column of numeric data, so it will always exist to be found. Thanks very much for your help both of you, I think I've had a problem on another sheet where the cell not being there to be found could be the issue so I'll check it out.