I found this great module on he
Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & " " & rng.Text
Next
Range("F2") = strConcat
End Sub

It works nicely to concatenate the user-selected cells, but I'd like the
user to be able to chose the destination cell. Is that possible?
I tried adding this line above the Range line:
x = Application.InputBox(prompt:="enter the value", Type:=1)
then changed Range("F2") = strConcat to Range(x) = strConcat

But I got an error.
Is it possible to do this?

Thank you,
Dee

This worked for me
Sub tryme()
Dim x As String
x = Application.InputBox(prompt:="Enter target cell address")
Range(x) = "hello, World"
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dee Sperling" <Dee wrote in message
...
I found this great module on he
Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & " " & rng.Text
Next
Range("F2") = strConcat
End Sub

It works nicely to concatenate the user-selected cells, but I'd like the
user to be able to chose the destination cell. Is that possible?
I tried adding this line above the Range line:
x = Application.InputBox(prompt:="enter the value", Type:=1)
then changed Range("F2") = strConcat to Range(x) = strConcat

But I got an error.
Is it possible to do this?

Thank you,
Dee

Thank you so much for the quick reply!

I changed my code to read:
Sub ConcatAndPaste()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & " " & rng.Text
Next
Dim x As String
x = Application.InputBox(prompt:="Enter target cell address")
Range(x) = strConcat
End Sub

I ran it and, when prompted, clicked the cell that I wanted to paste the
concatenated string into which put =$F$2 into the Input box. When I clicked
OK, I got an error. Debug shows
Range(x) = strConcat
highlighted in yellow.

Obviously I goofed. Can you tell me where?

Thanks again,
Dee



"Bernard Liengme" wrote:

This worked for me
Sub tryme()
Dim x As String
x = Application.InputBox(prompt:="Enter target cell address")
Range(x) = "hello, World"
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dee Sperling" <Dee wrote in message
...
I found this great module on he
Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & " " & rng.Text
Next
Range("F2") = strConcat
End Sub

It works nicely to concatenate the user-selected cells, but I'd like the
user to be able to chose the destination cell. Is that possible?
I tried adding this line above the Range line:
x = Application.InputBox(prompt:="enter the value", Type:=1)
then changed Range("F2") = strConcat to Range(x) = strConcat

But I got an error.
Is it possible to do this?

Thank you,
Dee

How about asking for both ranges?

Option Explicit
Sub ConcatSelection()
Dim InputRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim strConcat As String

Set InputRng = Nothing
Set DestCell = Nothing
On Error Resume Next
Set InputRng = Application.InputBox _
(Prompt:="Select the cells to concatenate", _
Default:=Selection.Address, Type:=8)
If InputRng Is Nothing Then
Exit Sub 'user hit cancel
End If
Set DestCell = Application.InputBox _
(Prompt:="Select the destination cell to concatenate", _
Type:=8).Cells(1)
If DestCell Is Nothing Then
Exit Sub 'user hit cancel
End If
On Error GoTo 0

strConcat = ""
For Each myCell In InputRng.Cells
strConcat = strConcat & " " & myCell.Text
Next myCell

If Len(strConcat) 0 Then
strConcat = Mid(strConcat, 2)
End If

DestCell.Value = strConcat

End Sub

Personally, I don't like this for most things.

I'd rather use a function in that receiving cell:

Like:
=multicat(a1:a10)

If you think you want to try this, take a look at JE McGimpsey's site:
http://mcgimpsey.com/excel/udfs/multicat.html



Dee Sperling wrote:

I found this great module on he
Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & " " & rng.Text
Next
Range("F2") = strConcat
End Sub

It works nicely to concatenate the user-selected cells, but I'd like the
user to be able to chose the destination cell. Is that possible?
I tried adding this line above the Range line:
x = Application.InputBox(prompt:="enter the value", Type:=1)
then changed Range("F2") = strConcat to Range(x) = strConcat

But I got an error.
Is it possible to do this?

Thank you,
Dee

--

Dave Peterson