Macro to add comments from cell values

[Duckymew]

Hey there,

I'm working on a spreadsheet to create header sheets for batches
basically it's a skeleton document to be filled out over and over.

on each row I have the file number, the status of the file and the dat
of the file.

Each file number is copied to another range of cells for ease of copyin
to another spreadsheet (a row instead of a column) and I want to be abl
to run a quick macro to enable me to add the date of the file as
comment on the alternative format of file numbers.

I know I ramble but wanted to be thorough, so I'll summarise:

I want to run the macro and have cell values (range = F11:F24) added a
comments on other cells (range = G11:T11)
(F11 as a comment in G11, F12 as a comment in H11 etc.)

If there's no date, I don't want a comment added.

As you may be able to tell - i'm new here...be gentle with me.

Cheer

 

[Paul Robinson]

Hi
See how this works for you. Name1 and Name2 are the names of two
sheets - I wasn't sure if you were transferring between sheets. They
can be the same name. Format changes the date format - you can change
it by putting in / or - or more or less d, m and y's. Cstr is there as
the AddComment command is looking for a text string and Cstr forces
that.

Sub tester()
Dim Range1 As Range
Dim Range2 As Range
Dim i As Long
Dim CommentNumber As Long
Set Range1 = Worksheets("Name1").Range("F11:F24")
CommentNumber = Range1.Count
Set Range2 = Worksheets("Name2").Range("G11").Resize(1, CommentNumber)
For i = 1 To CommentNumber
If Range1.Cells(i, 1).Value <> "" Then
Range2.Cells(1, i).AddComment Format(CStr(Range1.Cells(i,
1).Value), "dd mmm yyyy")
End If
Next i
End Sub

regards
Paul