Excel 2007
Been playing with this. A lot of guesswork, but I've made some progress. I'm
stuck at this point, however.


Public Function Location$()
    ' search back in a column of:
    ' name
    ' date
    ' location
    ' name
    ' date
    ' location
    ' name
    ' date
    ' location
    ' and copy the location from two days ago.

    Dim foundrow As Long
    Dim searchcol As Range
    Set searchcol = ActiveCell.Columns("A:A").EntireColumn

    foundrow = WorksheetFunction.Match( _
        CLng(ActiveCell.Offset(-1, 0).Range("A1")) - 2, _
        searchcol, 0)

    'Below works fine--as long as I want column C
    'Location = Range("C:C")(foundrow + 1, 1).Text

    'This one doesn't work and I don't know why
    Location = searchcol(foundrow + 1, 1).Text

End Function


Something fundamental I don't understand here. Thanks for any help.

Post a reply to this message