Comments into a cell without VB.
 

Hi, I hv a file with so many credit card numbers, each with corresponding
comments. I need these comments in the form of text in their corresponding
cells, without the use of VB. If not possible, give the simplest way.
thanks,
Amit.

Comments into a cell without VB.
 

"Amit Kumar Baidyaka" wrote:

Hi, I hv a file with so many credit card numbers, each with corresponding
comments. I need these comments in the form of text in their corresponding
cells, without the use of VB. If not possible, give the simplest way.

Amit

VB is the only way I'm afraid. Here is DAve Peterson's solution posted a few
days ago. It gives you more information than you asked for but you can stop
this by placing an apostophe before the line.

Sub ShowCommentsAllSheets()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As range
Dim mycell As range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set newwks = Worksheets.Add

newwks.range("A1:G1").Value = _
Array("Sheet", "Address", "Name", "Value", "Comment", "Author", "Doc
Author")

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.Text
.Cells(i, 6).Value = mycell.Comment.Author
.Cells(i, 7).Value = ActiveWorkbook.BuiltinDocumentProperties _
("Author") 'by Gord Dibben
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
range("A2:g2").Columns.AutoFit
Application.ScreenUpdating = True

End Sub


Regards
Peter

Comments into a cell without VB.
 

Let us say that in a cell we have the number 123456 and the comment is Chase
Visa.

1. First open the comment, select the text and do a CNTRL-C to copy it.
2. Click on the cell and then touch F2 and then CNTRL-V.
3. This will paste the comment into the cell after the previous contents and
you should see:


123456Chase Vise
in the cell
--
Gary's Student
gsnu200711


"Amit Kumar Baidyaka" wrote:

Hi, I hv a file with so many credit card numbers, each with corresponding
comments. I need these comments in the form of text in their corresponding
cells, without the use of VB. If not possible, give the simplest way.
thanks,
Amit.

Comments into a cell without VB.
 

"Gary''s Student" wrote:

Let us say that in a cell we have the number 123456 and the comment is Chase
Visa.

1. First open the comment, select the text and do a CNTRL-C to copy it.
2. Click on the cell and then touch F2 and then CNTRL-V.
3. This will paste the comment into the cell after the previous contents and
you should see:


123456Chase Vise
in the cell
--
Gary's Student
gsnu200711


Yes, but as Amit has many comments it will take some time to complete.

Peter

Comments into a cell without VB.
 

You are correct, but some people are shy of VBA. This small routine will run
down column A, grab any comments, and append them to the data already in the
cell:

Sub xrf_cmts()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Set r = Cells(i, 1)
Set cmt = r.Comment
If cmt Is Nothing Then
Else
s = cmt.Text
r.Value = r.Text & s
End If
Next
End Sub

--
Gary''s Student
gsnu200711

Comments into a cell without VB.
 

Neat!!

"Gary''s Student" wrote:

You are correct, but some people are shy of VBA. This small routine will run
down column A, grab any comments, and append them to the data already in the
cell:

Sub xrf_cmts()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Set r = Cells(i, 1)
Set cmt = r.Comment
If cmt Is Nothing Then
Else
s = cmt.Text
r.Value = r.Text & s
End If
Next
End Sub

--
Gary''s Student
gsnu200711