USer Defined Function

  • Thread starter Thread starter Excel Dummy
  • Start date Start date
E

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.
 
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)
 
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

Chip Pearson said:
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)
Click to expand...
 
Back
Top