Using a variable in the Range function

  • Thread starter Thread starter Dee Sperling
  • Start date Start date
D

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
 
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
 
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
 
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
 
Back
Top