Insert 500 comments from a list into 500 cells in a column

[MixedUpExcel]

Hi,

Can someone please help me with a macro for this.

If I have a list of 500 comments (simply as text in cells) in a spreadsheet (sheet 2, A1 to A500), how can I take those (automatically) and insert them as comments into Sheet 1, C15 to C514?

Sheet 1 is password protected but if that's a problem, I can manually remove the password whilst I run the macro to insert the comments.

Thanks in advance for any help with this.

Simon

 

[mikerickson]

Yes, unprotecting Sheet1 will help this macro run.

Sub test()
    Dim i As Long
    For i = 0 To 499
        Sheets("Sheet1").Range("C15").Offset(i, 0).NoteText Text:=Sheets("Sheet2").Range("A1").Offset(i, 0).Text
    Next i
End Sub

 

[MixedUpExcel]

Hi Mike,

Thanks for the quick response on this.

Works just how I'd hoped.

Appreciate your time on this.

Simon

 

[MixedUpExcel]

Just a thought Mike,

If my list of 500 (on Sheet 2) was broken, as in not all 500 cells had a comment which needed to be inserted on Sheet 1 (this would correspond with data I already had on Sheet 1) is there a way for me to get the loop to skip rows?

I was looking at something like the below but can't get it to work:

Sub test()


    Dim i As Long
    For i = 0 To 100
    
    If Not Sheets("Sheet2").Range("A" & i).Value = "" Then
    
        Sheets("Sheet1").Range("C15").Offset(i, 0).NoteText Text:=Sheets("Sheet2").Range("A1").Offset(i, 0).Text
    
    Else
    
    End If
    
    Next i
   
End Sub

Any suggestions please or am I looking at this the wrong way?

Thanks.

Simon

 

[MixedUpExcel]

I think I've got it working with this:

Sub test()


    Dim i As Long
    For i = 0 To 100
    
    If Not IsEmpty(Sheets("Sheet2").Range("A" & i + 1).Value) Then
    
        Sheets("Sheet1").Range("C15").Offset(i, 0).NoteText Text:=Sheets("Sheet2").Range("A1").Offset(i, 0).Text
    
    End If
    
    Next i
   
End Sub

Thanks.