Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to ensure workbook is saved before exiting Excel? | Excel Discussion (Misc queries) | |||
Ensure that required cells have beeb updated on saving the workboo | Excel Discussion (Misc queries) | |||
When opening a workbook, need to ensure user opens in a certain ce | Excel Discussion (Misc queries) | |||
how do I ensure that the sum of two cells does not exceed a set fi | Excel Worksheet Functions | |||
ensure backup function occurs once a month | Excel Programming |