Your Cells statements don't specify the sheet nor the workbook. When not
specified, VBA assumes the active sheet.

Fully clarify the Cells statement using a workbook or worksheet variable -
something like...

Dim wks as Worksheet

Set wks = ThisWorkbook.Worksheets("XYZ")

If LowestRate wks.Cells(rngStart.Row, lngCol).Value

OR

Dim wkb as Workbook

Set wkb = ThisWorkbook

If LowestRate wkb.Worksheets("XYZ").Cells(rngStart.Row, lngCol).Value

OR you can access the correct sheet by traveling up the object tree of your
rngStart variable

'rngStart.Parent = the worksheet the defined range is located in
If LowestRate rngStart.Parent.Cells(rngStart.Row, lngCol).Value

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"richardbok " wrote in message
...
I have written a function that searches for the lowest value in one
workbook... but this workbook is opened together with other workbooks
for analysis.

Part of my function is as follows:
If LowestRate Cells(rngStart.Row, lngCol).value Then
LowestRate = Cells(rngStart.Row, lngCol).value

And I perform within my workbook, =search_lowest(O11,2) where the data
to be search is from O11 to the last column.

Everytime I opened a new workbook, it seems that the reference goes to
a new workbook's O11 to last column. How do I ensure that does not
happen? Do I specify the full path of the workbook, worksheet in my
module? If so, what is the syntax for that? Thanks.

rb


---
Message posted from http://www.ExcelForum.com/