-1

I am running through about 500 iterations of the same type of procedure and I run into this error on the 227th iteration. I'm not sure why it works for all of the items before hand or why it stops working here. The generalized code looks like this:

sub macro()

Set ws = ThisWorkbook.Sheets("Sheet1")

for i = 1 to 500

    datarange1 = "f3:f50"
    
    Set range1 = ws.Range(datarange1)
    lupValue= Application.WorksheetFunction.Min(range1)

    With range1
        Set cellc = .Find(lupValue, LookIn:=xlValues)
        date_min = cellc.Offset(0, -4).Value
    End With
next

end sub

The code stops on the 'date_min = cellc.Offset(0, -4).Value' with the 'object variable error'

Any idea why it stops?

Warcupine
  • 4,460
  • 3
  • 15
  • 24
rawmud
  • 70
  • 9
  • is the value in cellc.Offset(0,-4).value There is no variable in cell b227. The range f3:f50 is populated with a different macro from values derived from an api. For this case, lupValue = 1959.12 and date_min = 8/21/2020 2:00:00. I expect cellc.offset(0,-4).value to return '8/21/2020 2:00:00' – rawmud Aug 26 '20 at 14:08

1 Answers1

0

If Find finds no match, it will return Nothing to cellc, this causes your runtime error. You have to check this with something like

if Not cellc is Nothing then
    date_min = cellc.Offset(0, -4).Value
Else
    ' ... think of a way to handle that case, maybe show an error.
End If

If your data in range f3:f50 contains no numeric values (or no data at all), the Min-function return 0, this probably causes the Find to fail.

Why, btw, do you loop 500 times about that? It will do 500 times the same (you are not using the counter i anywhere)

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • The min funciton returns a value. cellC returns nothing. There is a value in cellC that matches with the value I am trying to find. I'm going to try adding the 'after' function...cellC is the second row in the range, so maybe that is why it is erroring. – rawmud Aug 26 '20 at 14:15
  • Min returns the minimum numeric value within a range. If you then search for that minimum value using `Find`, this should always find that value - except if the range didn't contain any numeric values, in that case Min returns 0 and this cannot be found. When the error happen, check the value of `lupValue` with the debugger and have a look to `range1 `. – FunThomas Aug 26 '20 at 14:36
  • I added the 'after' to the find function and it did not work. I did a little side debugger and it is giving me strange results. I changed some values in the range to (2,3,4,5,6) and set the lupValue to one of those and passed it through the find function. It only returned the actual cell for 3. For 2,4,5,6 it returned either the second or third value in the range. – rawmud Aug 26 '20 at 15:15