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

R

richardbok

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
 
R

Robert Rosenberg

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top