USer Defined Function

[Excel Dummy]

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.

 

[Chip Pearson]

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 (e-mail address removed)

 

[Charles Williams]

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(xlToLeft).Value

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

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 (e-mail address removed)