split

[RobcPettit]

Hi, Im using split1 = Split(ActiveCell, ",") on the Active cell. The
Active cell contains numbers like 44,40,46 etc. What Id like to do is
after the split to use a For loop on split1. Like, For i = 1 to
Split1.count/ or ubound(split1). Tried both didnt work. On the above
example the count would be 3. Any ideas please. I could loop through
until I get an error.
Regards Robert

 

[Rick Rothstein \(MVP - VB\)]

See if this example helps you any...

Sub Test()
Dim X As Long
Dim Split1() As String
Split1 = Split(ActiveCell.Value, ",")
For X = 0 To UBound(Split1)
Debug.Print Split1(X)
Next
End Sub


Rick

 

[Chip Pearson]

Try some code similar to the following:

Dim R As Range
Dim W As Variant
Dim N As Long

For Each R In Range("A1:A10")
If R.Value <> vbNullString Then
W = Split(R.Text, ",")
For N = LBound(W) To UBound(W)
R(1, N + 2) = W(N)
Next N
End If
Next R


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JW]

Hi, Im using split1 = Split(ActiveCell, ",") on the Active cell. The
Active cell contains numbers like 44,40,46 etc. What Id like to do is
after the split to use a For loop on split1. Like, For i = 1 to
Split1.count/ or ubound(split1). Tried both didnt work. On the above
example the count would be 3. Any ideas please. I could loop through
until I get an error.
Regards Robert

split1 = Split(ActiveCell, ",")
MsgBox UBound(split1) + 1

Or
split1 = Split(ActiveCell, ",")
For i = 0 To UBound(split1)
MsgBox split1(i)
Next i

 

[Rick Rothstein \(MVP - VB\)]

Just a quick point of interest... the lower bound for arrays produced by the
Split function is always 0 (no matter what setting you have for the Option
Base); however, this is not the case for arrays produced by the Array
function.

Rick

 

[Chip Pearson]

the lower bound for arrays produced by the Split function is always 0

I know that, but it is a strong habit to use LBound for all arrays,
regardless of whether the LBound is fixed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

Yes, I figured you knew that... I was just putting the comment out there for
any readers out there following this thread.

Rick

 

[RobcPettit]

Thankyou all for your replys, very much appreciated.
Regards Robert