Using a variable in the Range function

[Dee Sperling]

I found this great module on here:
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

 

[Bernard Liengme]

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

 

[Dee Sperling]

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

 

[Dave Peterson]

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