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 date of the file.

Each file number is copied to another range of cells for ease of copying to another spreadsheet (a row instead of a column) and I want to be able to run a quick macro to enable me to add the date of the file as a 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 as 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.

Cheers

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

On Feb 15, 2:50*pm, Duckymew wrote:
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 date
of the file.

Each file number is copied to another range of cells for ease of copying
to another spreadsheet (a row instead of a column) and I want to be able
to run a quick macro to enable me to add the date of the file as a
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 as
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.

Cheers

--
Duckymew