title: VBA: Applying MAX on an address from UDF in worksheet
body: <p>I have written a UDF that returns the address of a range (a column without header) from a worksheet. The output is a string like "$A$2$A$5". In another worksheet, I want to apply several worksheet functions, e.g., MAX, on the corresponding range of cells A2 to A5.
  The combination of MAX and the UDF leads to an error message (#Value) while when replacing the UDF with the corresponding string value: MAX($A$2$A$5) works.
  Is there a way to make this work? Code:</p>
  
  <pre><code>Public Function SELECTH(SheetName As String, HeaderName As String) As String
  
     Dim ColIndex As Integer
     Dim MaxRowIndex As Integer
  
     ColIndex = ActiveWorkbook.Worksheets(SheetName).Rows(1).Find(HeaderName).Column
     MaxRowIndex = ActiveWorkbook.Worksheets(SheetName).Cells(1, 1).End(xlDown).Row
  
     SELECTH = ActiveWorkbook.Worksheets(SheetName).Range(Cells(2, ColIndex), Cells(MaxRowIndex, ColIndex)).Address()
  
  End Function
  </code></pre>