PDA

View Full Version : Need a little help with my (working) code regarding putting headers into comments



markpem
02-16-2015, 05:00 AM
Hello

I currently have the following code (which works a treat) and it puts all the data in A2, B2, C2, D2 etc into a comment in A2.

However I also have headers in A1, B1, C1, and I would like them adding like the screenshot if anyone could give me the code?

Thanks!

Current Code:-


Dim cmt As Comment
Dim rng As Range

Set rng = Range("A1")

Do
Set cmt = rng.Comment

If cmt Is Nothing Then
rng.AddComment
Set cmt = rng.Comment
End If

cmt.Text Join(Application.Transpose(Application.Transpose(rng.Resize(, 6).Value)), Chr(10))

cmt.Visible = True ' optional

Set rng = rng.Offset(1)
Loop Until rng.Value = ""



Click on picture:-
12857

Aflatoon
02-16-2015, 05:20 AM
Your code doesn't quite match your picture but it will be something like:

Dim cmt As Comment Dim rng As Range
Dim n As Long
Dim sComment As String


Set rng = Range("A1")


Do
Set cmt = rng.Comment


If cmt Is Nothing Then
rng.AddComment
Set cmt = rng.Comment
End If
For n = 1 To 6
sComment = sComment & vbLf & Cells(1, n).Value & ": " & Cells(rng.Row, n).Value
Next n
cmt.Text Mid$(sComment, 2)


cmt.Visible = True ' optional


Set rng = rng.Offset(1)
Loop Until rng.Value = ""

mancubus
02-16-2015, 05:42 AM
hi.

i would include below line in the loop as well...


cmt.Shape.TextFrame.AutoSize = True

markpem
02-16-2015, 06:36 AM
Hello Aflatoon

Thankyou for your help, it's 99% working. However what is happening is this (I have taken a picture to explain it a lot better);-
(it should not be copying the previous data)

12859

markpem
02-16-2015, 06:37 AM
Hello Mancubus

I have done this and the formatting is much better now, thanks agian for your input and help.


hi.

i would include below line in the loop as well...


cmt.Shape.TextFrame.AutoSize = True

Aflatoon
02-16-2015, 06:55 AM
Sorry that was silly. Add this after the Do line:

sComment = vbNullstring