How to ensure function does not refer to cells outside of workbook?
 

I have written a function that searches for the lowest value in on
workbook... but this workbook is opened together with other workbook
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 dat
to be search is from O11 to the last column.

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

r

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

How to ensure function does not refer to cells outside of workbook?
 

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/