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

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


"RobcPettit" wrote in message
oups.com...
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

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)


"RobcPettit" wrote in message
oups.com...
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

On Oct 13, 5:01 pm, RobcPettit wrote:
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

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" wrote in message
...
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)


"RobcPettit" wrote in message
oups.com...
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

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)" wrote in
message ...
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" wrote in message
...
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)


"RobcPettit" wrote in message
oups.com...
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

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

Rick


"Chip Pearson" wrote in message
...
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)" wrote in
message ...
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" wrote in message
...
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)


"RobcPettit" wrote in message
oups.com...
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

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