Grab Parents?

[Kidaz]

Hi guys.

I am trying to write a method to take a range of cells, and write them
into a 'list cell' in the following format:
=E6 & CHAR(10)& G4 & CHAR(10) & K22
for exmample. (CHAR(10) is the newline character in Excel btw.)

I am using an inputbox to select the desired cells to append to the
list cell.
I have got as far as selecting them, but have no idea how to add them
in this way. Any ideas?

Public Sub AddParents()
Dim parents As Range
Dim parent As Range
Dim currParents As Range

Set parents = Application.InputBox("Select a range", Type:=8)
If Not parents Is Nothing Then
Set currParents = ActiveCell
For Each parent In parents
If Not InRange(parent, currParents) Then
'add parent to currParents in the above format
End If
Next parent
End If

End Sub

 

[JE McGimpsey]

One way:

First - "parent" is a reserved word in VBA. It's best not to use it -
VBA won't be confused, but those trying to maintain your code might be.

Public Sub AddParents()
#If Mac Then
Const sSEPARATOR As String = "&CHAR(13)&"
#Else
Const sSEPARATOR As String = "&CHAR(10)& "
#End If
Dim rParents As Range
Dim rParent As Range
Dim sFormula As String

Set rParents = Application.InputBox("Select a range", Type:=8)
If Not rParents Is Nothing Then
sFormula = "=" & ActiveCell.Text & sSEPARATOR
For Each rParent In rParents
If Intersect(rParent, ActiveCell) Is Nothing Then _
sFormula = sFormula & rParent.Text & sSEPARATOR
Next rParent
ActiveCell.Formula = Left(sFormula, Len(sFormula) - _
Len(sSEPARATOR))
End If
End Sub