Here's the code I'm using:
--------- Code Start ---------
....
'Found the cell, now get the entire region of text
vVari = ActiveCell.CurrentRegion
SearchBook.Close
'Now build the string into a single variable
For counter = 1 To UBound(vVari, 1)
sTemp = sTemp & vVari(counter, 1) & ": " & vVari(counter, 2) & vbLf
Next counter
GetSAPVariables = sTemp
....
--------- Code End ---------

The values in the 2D range are text. Here is a small sample of what sTemp
looks like:
D-TARGET WOS: 12
E-TARGET WOS: 14
Future Sales Forecast(No.of Weeks): 8
Month After Future Month Sales Forecast(8 wks default): 18/2007 - 25/2007

Using vbCRLF leaves a special character at the end of each sentence (a
square) which is why I'm just using the LineFeed command.

Then I build the comment:
--------- Code Start ---------
....
With Sheets(SheetName).Range(COMMENT_LOCATION)
.AddComment 'Adds the comment to the specific location
With .Comment
.Text Text:=CommentText 'Populate the comment...
.Shape.ScaleWidth 3.65, msoFalse, msoScaleFromBottomRight
'...and size it
.Shape.ScaleHeight 7.89, msoFalse, msoScaleFromTopLeft
End With
End With
....
--------- Code End ---------

Does this help? I thought there may be a simpler way but the above code
puts the text into the Comment almost like how it looks in the [original]
sheet range (I added the colon to separate the two values).

Toby

"Rick Rothstein (MVP - VB)" wrote:

You might want to expand and clarify your question a little bit. The
CurrentRegion is (probably) made up of several cells in a 2-D arrangement...
How did you want the text from each cell combined so it can be stored in a
"variable"? What do you want linking the individual pieces of text from each
cell (tabs, commas, spaces, something else for the columns, vbCrLf for the
rows)? Or did you perhaps mean you want to store the text in a 2-D String
array?

Rick


"Air_Cooled_Nut" wrote in message
...
How do I copy the CurrentRegion into a variable? The CurrentRegion is
text
and that's all I want to put into the variable (text, no formatting). TIA
:-)