USer Defined Function
 

Dear All,

I have the following user defined function in a workbook (taken from a
reply to an earlier post I made)

Public Function Findlastcell(MyAddress As Range)

Application.Volatile
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value

End Function

It works perfectly well in lets say file1.xls until I open another file
(file2.xls).

The formula then takes the values from file2.xls. I can rectify this by
going back to file1.xls and then pressing F9.

How do I make the code above specific to file1.xls only.

Excel Dummy.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

USer Defined Function
 

Change
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value
to
Findlastcell = Application.Caller.Parent.Cells(MyAddress.Row,
256).End(xlToLeft).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Excel Dummy" wrote in message
...

Dear All,

I have the following user defined function in a workbook (taken from a
reply to an earlier post I made)

Public Function Findlastcell(MyAddress As Range)

Application.Volatile
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value

End Function

It works perfectly well in lets say file1.xls until I open another file
(file2.xls).

The formula then takes the values from file2.xls. I can rectify this by
going back to file1.xls and then pressing F9.

How do I make the code above specific to file1.xls only.

Excel Dummy.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

USer Defined Function
 

To extend Chip's solution to the case where MyAddress is not on the same
sheet as the cell the function is called from it probably should be:

Findlastcell = MyAddress.Parent.Cells(MyAddress.Row,256).End(xlTo Left).Value

regds
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Chip Pearson" wrote in message
...
Change
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value
to
Findlastcell = Application.Caller.Parent.Cells(MyAddress.Row,
256).End(xlToLeft).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Excel Dummy" wrote in message
...

Dear All,

I have the following user defined function in a workbook (taken from a
reply to an earlier post I made)

Public Function Findlastcell(MyAddress As Range)

Application.Volatile
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value

End Function

It works perfectly well in lets say file1.xls until I open another file
(file2.xls).

The formula then takes the values from file2.xls. I can rectify this by
going back to file1.xls and then pressing F9.

How do I make the code above specific to file1.xls only.

Excel Dummy.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/