Result in Debug different than in Direct window

[DaOtH]

Guys,
funny problem, trying to execute a bit of code.
Problem is that the results are incorrect when running normally (or in debug),
however, when executing the same little step, using the same variables, results in a correct result.

little line is:

Set r = Range(strRange).Find(What:=CDate(strDat), LookIn:=xlValues _
            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If r Is Nothing Then
....

In debug r = Nothing in direct window r=Something
(i am running the code in the direct window during the debugging)


"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[SkipVought]

Hi,

Quallify the range fully, including a sheet object and workbook object if three are multiple workbooks open as well.

Skip,

_[red][/red]

 

[DaOtH]

Hmm, i see the logic in defining the workbook object.

But i am using named ranges, so to get the sheet the named range is on, i need to get the info from the properties of the named range. However, the range can not be accessed without defining the sheet it is on...

(havent yet been able to try it, but sounds funny to me)

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[SkipVought]

There are WORKBOOK level ranges and SHEET level ranges.

When yo uinitially define a named range, it is a WORKBOOK level name. If you define a range on another sheet using a workbook level name, it geive is a SHEET level name.

Try using the WORKBOOK object with the Range...

Set r = oSomeWrkbk.Range(strRange).Find(What:=CDate(strDat), LookIn:=xlValues _
            , LookAt:=xlWhole, SearchOrder:=xlByRows,

Skip,

_[red][/red]

 

[DaOtH]

No .range available on the Workbook Object

However, i do not think it has to do with the accessing of the range itself, since the following does work, however it is offcourse not a perfect solution.

  For intR = 1 To Range(strRange).Rows.Count
    For intC = 1 To Range(strRange).Columns.Count
      If Range(strRange).Value2(intR, intC) = lngDate Then
                            pfFunction = True
                            Exit Function
      End If
    Next intC
  Next intR

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[SkipVought]

Isn't this basicly what your code is doing...

    Dim r As Range
'...
    For Each r In Range(strRange)
        If r.Value2 = lngDate Then
            pfFunction = True
            Exit Function
        End If
    Next

Skip,

_[red][/red]

 

[DaOtH]

Welll... yeah... makes it a bit nicer,
Still funny why the .Find does not work
Anyway, don't think there will be much difference in the result / runtime.

Thnxs

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963