Just one click

[sasa]

Hi,
this is my problem. I have an entire column full of names.
something like this:

a1 = Tom John Peter Ralph ( many names in the same cell)
a2 = free
a3 = mary
a4 = free
a5 = free
a6 = Ron
and so on
in other words any cell of the entire column can include one or more names or
can be empty.

What I need ( if it is possible is a macro (just one click after I have select four cells everywhere in the column put the names one for cell just as they are, in my example
A1 = Tom
a2 =John
a3 = Peter
a4 = Ralph

Thanks in advance
sasa

[Bob Phillips]

So are you saying that you will select say A1:A4 and the values in A1 get split acroos those cells? What about data alraedy in one of them? Which cell should be split across them. What if there is nothing in any of them.

[RonMcK]

Sasa,

What do you want to do with the data in 'old' A3 and A6? Is there data in cells to the right of A1 in row 1 that is unique to those names and that needs to be pasted to rows of the new 'single name' cells?

Thanks,

[sasa]

Well, as the four cells can not have more than four names the free cells can be occupied at all.
example:
from a1 to a 4
a1 = a b c d
a2 = free
a3 = free
a4 = free
so:
a1 = a
a2 = b
a3 = c
a3 = d

if
a1 = free
a2 = a b c (three items)
a3 = free
a4 = free
so
a1= a
a2=b
a3= c
a4 = free

if
a1= free
a2 = free
a3 = free
a4= a b
then a1 = a
a2 = b
a3= free
a4= free

thanks

[Bob Phillips]

Public Sub Test()
Dim cell As Range
Dim tmp As Variant
Dim i As Long

For Each cell In Selection

If cell.Value <> "" Then

tmp = Split(cell.Value, " ")
Exit For
End If
Next cell

If Not IsEmpty(tmp) Then

For i = LBound(tmp) To UBound(tmp)

Selection.Cells(i - LBound(tmp) + 1, 1).Value = tmp(i)
Next i
End If
End Sub

[RonMcK]

Bob,

This seems to work only on the first cell in the selection.

I entered and selected:
a1=Ronald John McKenzie Margot Ann McKenzie
a2=free
a3=free
a4=Nancy Ann Caitlin Jessica Peter
a5=free
a6=Jason Chris Rebecca Reese
a7=free
a8=free
a9=free
And 'test' gave me back:
a1=Ronald
a2=John
a3=McKenzie
a4=Margot
a5=Ann
a6=McKenzie
a7=free
...
a15=free
What's a solution? To add an array, populate it from the worksheet column, then, parse out individual names and write them to cells starting from the top of the column or selection?

Thanks,

[Bob Phillips]

That is true Ron. I was going more from post #4 as he didn't answer my previous questions.

[Bob Phillips]

To answer your question, I would probably extract all values into an array, and then output them as my code does, but from each array item.

[RonMcK]

Bob,

So, parse tmp's contents (an array in a variant) into a larger array? Read the entire range of entries in that manner. Then, do the output back to the column in one pass?

I'll try that.

Thanks,

[RonMcK]

Bob,

How to I determine the range of cells in 'selection'?

Thanks,

[RonMcK]

Bob, sasa, et al,

Here's my solution for processing a multi-line selection; some of the lines can be blanks. The list will begin in the first cell of the selection.

Str_split is a function that emulates Window's 'split' on the Mac.
Public Sub Test()
Dim cell As Range
Dim tmp As Variant
Dim myArry() As String
Dim ArrySiz As Long
Dim NewArrySiz As Long
ArrySiz = 100
NewArrySiz = ArrySiz
Dim i As Long
Dim j As Long
ReDim myArry(ArrySiz)

j = 0
For Each cell In Selection

If cell.Value <> "" Then

tmp = str_split(cell.Value, " ")

For i = LBound(tmp) To UBound(tmp)

myArry(j) = tmp(i)
j = j + 1
If j > NewArrySiz Then
NewArrySiz = NewArrySiz + ArrySiz
End If
Next i
End If
Next cell

If myArry(1) <> "" Then

For i = LBound(myArry) To UBound(myArry)

Selection.Cells(i - LBound(myArry) + 1, 1).Value = myArry(i)
Next i
End If
End Sub

[RonMcK]

Bob,

How to I determine the range of cells in 'selection'?

Thanks,
Bob and all you lurking experts,

Is there a simple answer to this query? I want to tweak this macro so it compares the amount of same needed for the names in the list so it can ask how to handle cells that it will overwrite (accept overwrite or insert additional cells/rows).

Thanks,

[mdmackillop]

You can use Intersect to check if a cell is in the selection.

if not intersect(selection,cell) is nothing then

[RonMcK]

Malcolm,

Thanks!