I not really an "expert" on this sort thing, but the approach you used is
probably how I would have tackled the problem... I am not aware of any
simpler method. Perhaps one of the other regular contributors may know of
such a simpler method and will post it later on.

Rick


"Air_Cooled_Nut" wrote in message
...
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
:-)