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!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Function() don't have the capacity to add the comment of the current cell.

Sub() only can able to do the changes.

Function() can able to retrieve the source cell comment and show it in current cell.

Do you mean that it is impossible to add a previously copied comment to an active cell using Excel function in VBA? That would be a bad news.

Originally Posted by Szczesiu

Do you mean that it is impossible to add a previously copied comment to an active cell using Excel function in VBA? That would be a bad news.

Yes, function can't do the copy paste process. Instead function can able to display that comment as text string.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Sub GetComment()
Dim r As Range

Application.ScreenUpdating = False

For Each r In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).Cells
    If Not Range(r.Value).Comment Is Nothing Then
        With r.Offset(, 1)
            .ClearComments
            .AddComment (Range(r.Value).Comment.Text)
            .Comment.Visible = False
        End With
    End If
Next r

Application.ScreenUpdating = True

End Sub

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button