Stepping through with F8 the code runs, stops in standard execution with F5

Question

I've written a macro which sums numbers, grouping by year and by month based on our projection model.

It runs from start to end when stepping through with F8.
It stops immediately in standard execution with F5.

The first trouble is

Runtime error 91

in

issmIndex = Range("A1:Z1").Find("ck.IssMon").Column  'issmIndex an integer

Originally I tried Application.WorksheetFunction.Match(...) but had the same problem: runs in debug, but not in execute (Error 1004 instead).

I considered it could have been an Excel version issue (the Match function has a different name in the Italian version). I switched to a more neutral Find, but still no luck.

Answer 1

When you have an error with a line that is a combination of several commands, try breaking it down into the individual steps.

For example, this works:

Sub findDemo()
  Const toFind = "blah"
  Dim rg As Range, f As Range
  Set rg = Range("A2:C5")
  Set f = rg.Find(toFind)
  If f Is Nothing Then
    Stop 'not found
  Else
    Debug.Print "found in column #" & f.Column
  End If
End Sub

Also see the example in the documentation for Range.Find().