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 :-)

The below seems to work:
Dim vVari as Variant
vVari = ActiveCell.CurrentRegion

Trouble is, I have to loop through the array and build the text string into
another variable. The goal of all this is to dump the contents of the
CurrentRegion into a cell Comment. Suggestions welcomed!

"Air_Cooled_Nut" wrote:

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 :-)

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
:-)

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
:-)

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
:-)

I haven't really followed all this but it seems a bit arbitary to place
contents from an unknown range size into a comment. Anyway, here's a
different approach to play with.

Sub test()
Dim s As String
Dim dObj As DataObject ' need to temporarily add a Userform
Dim cmt As Comment

Set rng = Range("A1:C10") ' change to some current region
rng.Value = "ABC" ' just for testing

Set dObj = New DataObject
rng.Copy

dObj.GetFromClipboard
s = dObj.GetText

s = Replace(s, vbCr, "")
s = Replace(s, vbTab, " | ")

Range("E1").Comment.Delete
Set cmt = Range("E1").AddComment(s)
cmt.Shape.TextFrame.AutoSize = True
End Sub

Regards,
Peter T


"Rick Rothstein (MVP - VB)" wrote in
message ...
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
:-)

Thanks Peter, that looks like an approach I will follow up on. I like the
..AutoSize...I didn't know about that :-) Looks like I'll be doing some more
work tomorrow.

The range is two columns wide but the number of rows can vary. It's a list
of variables the user implemented in a SAP report (which was exported as
Excel format). The variable list is placed inside a Comment for the user's
reference in the metrics workbook as a "nice to have" feature. We needed to
keep all info contained within the one metrics sheet without cluttering it
up. The folks who use the metrics workbook loved the idea because it's very
easily and quickly accessed and so what the customer wants...

Thanks again,
Toby

"Peter T" wrote:

I haven't really followed all this but it seems a bit arbitary to place
contents from an unknown range size into a comment. Anyway, here's a
different approach to play with.

Sub test()
Dim s As String
Dim dObj As DataObject ' need to temporarily add a Userform
Dim cmt As Comment

Set rng = Range("A1:C10") ' change to some current region
rng.Value = "ABC" ' just for testing

Set dObj = New DataObject
rng.Copy

dObj.GetFromClipboard
s = dObj.GetText

s = Replace(s, vbCr, "")
s = Replace(s, vbTab, " | ")

Range("E1").Comment.Delete
Set cmt = Range("E1").AddComment(s)
cmt.Shape.TextFrame.AutoSize = True
End Sub

Regards,
Peter T


"Rick Rothstein (MVP - VB)" wrote in
message ...
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
:-)