Using the result of ADDRESS() function as a range for VBA function

Hi everybody,

I'm using the excel ADDRESS() function in a formula like "=ADDRESS(MATCH(B43;$A$1:$A$411;0);1)" and as a result I get the matching cell address, say it's "$A$270".

Now what I want to do is to use this result as an address for a VBA function, that looks like this:

Function copycomment()

Range(ActiveCell.Offset(0, -1).Value).Copy
ActiveCell.PasteSpecial xlPasteComments

End Function

With the function above I would like to get the value of a cell that is left to the active cell, use it as a range for to copy desired cell and paste only a comment of it to the active cell.

Using cell range as an argument for a function would make a more universal solution, and I also tried it, but failed. I wanted to use this function like: "=copycomment(C43)", where "C43" is an address of cell with the ADDRESS() function result.

My problem is generally how to pass the cell value (the result of the ADDRESS() function) as a range for my VBA function.

Any suggestion will be appreciated!