Assign Selection to a Variable

[oitbso]

I have selected a few rows that contain text on a worksheet. I would like to assign the text in these rows to a variable so that I wind up with a text string in the variable. I can assign the range to an array and then stepthrough the array and build my text string, or I can copy the selection and assign the clipboard contents to a variable. Both of these methods work and achieve the desired result, but they seem unwieldy. There must be a simpler way to do this. Can someone point me in the right direction?..Thanks, Ron

 

[GS]

(e-mail address removed) explained on 2/27/2012 :

I have selected a few rows that contain text on a worksheet. I would like to
assign the text in these rows to a variable so that I wind up with a text
string in the variable. I can assign the range to an array and then step
through the array and build my text string, or I can copy the selection and
assign the clipboard contents to a variable. Both of these methods work and
achieve the desired result, but they seem unwieldy. There must be a simpler
way to do this. Can someone point me in the right direction?..Thanks, Ron

If the text is broken into several cells on each row then there's no
'simple' solution such that the text could be directly assigned to a
string variable.

Assigning the range to a variant variable results in a 2D array that
you'll have to iterate for each element of text...

Dim vTextIn As Variant
vTextIn = <SelectedRange>

Dim i&, j& 'as long
Dim sTextOut$ 'as string
For i = LBound(vTextIn) To UBound(vTextIn) '#rows
'Join the text with a space or other delimiter as desired
For j = LBound(vTextIn, 2) To UBound(vTextIn, 2) '#cols
sTextOut = sTextOut & " " & vTextIn(i, j)
Next 'j
Next 'i
'Trim the leading space
sTextOut = Mid$(sTextOut, 2)


I don't use the clipboard object for this type of process and so I
can't offer any advice on that<g>.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[John Coleman]

I have selected a few rows that contain text on a worksheet.  I would like to assign the text in these rows to a variable so that I wind up with atext string in the variable.  I can assign the range to an array and then step through the array and build my text string, or I can copy the selection and assign the clipboard contents to a variable.  Both of these methods work and achieve the desired result, but they seem unwieldy.  There must be a simpler way to do this.  Can someone point me in the right direction?..Thanks, Ron

I don't know if this is less unwieldy, but if the range is small
enough you can use the selection itself as the array:

Function SelectToString() As String
Dim theString As String
Dim cell As Range
With Selection
For Each cell In Selection.Cells
theString = theString & IIf(theString = "", "", " ") &
cell.Text
Next cell
End With
SelectToString = theString
End Function

Sub test()
MsgBox SelectToString
End Sub


If A1 to A4 contain "This", "is", "a", "test" respectively and the
selection is A1:A4 then the function returns "This is a test". The IIf-
space insertion part could of course be deleted if you don't want
spaces between strings.

If the selection is 2-dimensional then you might not like the order in
which the loop iterates through the cells - but in that case you could
treat Selection as a 2-d array with the .Cells(i,j) property.