"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