Hi
Try this. I think this is what you are trying to do.
Dim myRng ' As Range
Dim NewString As String
Sub steven()
Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to concatenate",
Type:=8)
If myRng Is Nothing Then End
If myRng.Cells.Count > 5 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If
For cell = 1 To myRng.Cells.Count
NewString = NewString & myRng.Cells(cell, 1).Value & " "
myRng.Cells(cell, 1).ClearContents
Next
myRng.Cells(1, 1) = NewString
myRng.MergeCells = True
End Sub
Regards,
Per
"Per Jessen" <(E-Mail Removed)> skrev i meddelelsen
news:(E-Mail Removed)...
> Hi
>
> Set myRng = Application.InputBox(prompt:="Select cell to move data to",
> Type:=8).Cells(1)
>
> Change to Type:= 8 as shown above in all input statements.
>
> Regards,
> Per
>
> "SteveDB1" <(E-Mail Removed)> skrev i meddelelsen
> news:96595150-F2F5-4906-887B-(E-Mail Removed)...
>> Per,
>> Thank you.
>> My next question.
>> I've tried modifying what you stated to be more generic-- each row group
>> will change for each occurrence.
>> I tried the following, and it did not work.
>> the error that I get is that the object variable, or with block variable
>> is
>> not set. And in setting a watch on each of my myRng_N variables, they are
>> "nothing."
>> When I placed the error elements in, they threw errors.
>> What have I missed here?
>> ----------------------------------------------------------------------------------------
>>
>> Dim myRng As Range
>> Dim myRng1 As Range
>> Dim myRng2 As Range
>> Dim myRng3 As Range
>> Dim myRng4 As Range
>> Dim myRng5 As Range
>>
>>
>> Set myRng = Nothing
>> On Error Resume Next
>> Set myRng = Application.InputBox(prompt:="Select cell to move data to",
>> Type:=2).Cells(1)
>> On Error GoTo 0
>> 'If myRng Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>> Set myRng1 = Nothing
>> On Error Resume Next
>> Set myRng1 = Application.InputBox(prompt:="Select first cell to move data
>> from.", Type:=2).Cells(1)
>> ' On Error GoTo 0
>> 'If myRng1 Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>> Set myRng2 = Nothing
>> On Error Resume Next
>> Set myRng2 = Application.InputBox(prompt:="Select second cell to move
>> data
>> from.", Type:=2).Cells(1)
>> ' On Error GoTo 0
>> 'If myRng2 Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>> Set myRng3 = Nothing
>> On Error Resume Next
>> Set myRng3 = Application.InputBox(prompt:="Select third cell to move data
>> from.", Type:=2).Cells(1)
>> 'On Error GoTo 0
>> 'If myRng3 Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>>
>> Set myRng4 = Nothing
>> On Error Resume Next
>> Set myRng4 = Application.InputBox(prompt:="Select last cell to move data
>> from.", Type:=2).Cells(1)
>> On Error GoTo 0
>> 'If myRng4 Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>> Set myRng5 = Nothing
>> On Error Resume Next
>> Set myRng5 = Application.InputBox(prompt:="Select cells to merge.",
>> Type:=2).Cells(1)
>> On Error GoTo 0
>> 'If myRng5 Is Nothing Then
>> 'Exit Sub 'user hit cancel.
>> 'End If
>>
>> myRng.Value = myRng.Value & myRng1.Value & myRng3.Value & myRng4.Value
>>
>> myRng2.ClearContents
>> myRng3.ClearContents
>> myRng4.ClearContents
>>
>> myRng5.Select
>> With Selection
>> .MergeCells = True
>> End With
>>
>> End Sub
>> -----------------------------------------------------------------------
>>
>> Best.
>>
>>
>> "Per Jessen" wrote:
>>
>>> Hi
>>>
>>> Suppose you want to edit contents in A1
>>>
>>> Range("A1").Value = Range("A1").Value & Range("B1").Value
>>> Range("B1").ClearContents
>>>
>>> Hopes it helps.
>>>
>>> Regards,
>>> Per
>>>
>>> "SteveDB1" <(E-Mail Removed)> skrev i meddelelsen
>>> news:A9B0E278-50EC-47D5-AF2D-(E-Mail Removed)...
>>> > Hi all.
>>> > We have a number of workbooks which list documents, in a form which
>>> > we're
>>> > calling an abstract of title.
>>> > In our older workbooks, many of the abstracts list the sellers, and
>>> > buyers
>>> > with 4 row, single column groups. all as unmerged cells.
>>> >
>>> > With our newer formats, we now have these 4 row, 1 column groups
>>> > merged
>>> > into
>>> > a single cell. It just makes for a cleaner appearing layout.
>>> >
>>> > This morning I tried recording a macro to select row 2, and paste its
>>> > contents into row 1 with already existing data. I then selected row 3,
>>> > and
>>> > pasted that cell's contents into those of row 1, and did the same with
>>> > row
>>> > 4.
>>> >
>>> > Once this was complete, I merged the 4 rows, and turned on word wrap.
>>> > The problem that I saw once I went in to edit the code was that it
>>> > treated
>>> > the contents as an ActiveCell.FormulaR1C1.
>>> >
>>> > Which of course gives the string of contents.
>>> >
>>> > I then tried recording another macro to do the same thing by
>>> > copy/paste,
>>> > and
>>> > cut/paste. Once I activated the destination cell, to paste the
>>> > contents of
>>> > my
>>> > source cell, the cut/copy deactivated, and would not allow me to paste
>>> > the
>>> > source contents to my destination cell.
>>> >
>>> > As I need this to be more generic to cover all instances of this, I'm
>>> > curious as to what else I can use to accomplish this same goal,
>>> > without
>>> > over
>>> > writing the destination cell's existing contents.
>>> >
>>> > How do I select a cell, cut its contents, and paste those contents
>>> > into a
>>> > cell with already existing contents-- without overwriting those
>>> > contents--
>>> > by
>>> > use of a macro?
>>> >
>>> > The merging I can handle.
>>> > Thank you for your helps.
>>> >
>>>
>>>
>
Similar Threads
