Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grab Parents?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grab Parents?
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 In article . com, "Kidaz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number grab | New Users to Excel | |||
grab detail when = 1 | Excel Worksheet Functions | |||
Grab name of folder | Excel Programming | |||
First Letters Grab | Excel Discussion (Misc queries) | |||
Grab Password Name | Excel Programming |