In Adding Comments, I read the data from a text file and created comments with one of the fields because it was large. I mentioned in that post that the way I wouldn’t do it is to import the text file normally, then a run macro. I don’t think it’s necessarily a bad way, it’s just not my preference.
I start by importing the text file using File > Open. That brings up the Import Text wizard and results in a sheet like this:
In a separate file, I have a macro. The separate file could be an add-in, for instance, but it doesn’t have to be. The code just can’t be in a text file.
Sub MoveColumnToComment()
Dim ws As Worksheet
Dim rCell As Range
Dim rRng As Range
Set ws = ActiveSheet
Set rRng = Intersect(ws.Columns(1), ws.UsedRange)
For Each rCell In rRng.Cells
rCell.AddComment rCell.Offset(0, 1).Value
Next rCell
ws.Columns(2).Delete
End Sub
Dim ws As Worksheet
Dim rCell As Range
Dim rRng As Range
Set ws = ActiveSheet
Set rRng = Intersect(ws.Columns(1), ws.UsedRange)
For Each rCell In rRng.Cells
rCell.AddComment rCell.Offset(0, 1).Value
Next rCell
ws.Columns(2).Delete
End Sub
Once the code is run, I’m left with:
Posting code? Use <pre> tags for VBA and <code> tags for inline.