Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

UBound(array) - LBound(array) + 1

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"madeleine" wrote in message
ups.com...
Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

Show us some sample data and the expected results based on that data.
I think that we could you help you better then.

Cheers,
Jason Lepack

madeleine wrote:
Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

iArrayCount = UBound(MyArray) + 1 - LBound(MyArray)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"madeleine" wrote in message
ups.com...
Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

Thanks very much to all of you for your prompt responses and instant
help, Ubound did the trick just perfectly. Ah I may actually have a
weekend.

Thanks again

Madeleine
Jon Peltier wrote:

iArrayCount = UBound(MyArray) + 1 - LBound(MyArray)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"madeleine" wrote in message
ups.com...
Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

would this work?

s = 10.1.2.5
InStr(1, s, ".") + 1

--


Gary


"Tom Ogilvy" wrote in message
...
You approach sounds like the long way around the block.

s = 10.1.2.5
level = len(s) - len(replace(s,".","")) + 1

so
Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
cnt1 = len(checker_1)-len(replace(checker_1,".")) + 1
checker_2 = ActiveSheet.Range("A4").Value
cnt2 = len(checker_2)-len(replace(checker_2,".")) + 1
checker_3 = ActiveSheet.Range("A13").Value
cnt3 = len(checker_3)-len(replace(checker_3,".")) + 1
End Sub

Also You can't refer to variables using something like name & counter or
"Checker_" & counter

You would need array for that


Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String

Dim cnt(1 to 3) as Long


Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
cnt(1) = len(checker_1)-len(replace(checker_1,".")) + 1
checker_2 = ActiveSheet.Range("A4").Value
cnt(2) = len(checker_2)-len(replace(checker_2,".")) + 1
checker_3 = ActiveSheet.Range("A13").Value
cnt(3) = len(checker_3)-len(replace(checker_3,".")) + 1

for i = lbound(cnt) to ubound(cnt)
msgbox "Checker_" & i & ": " & cnt(i)
Next

End Sub

--
Regards,
Tom Ogilvy

"madeleine" wrote:

Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine

As I understand the request, Not except by coincidence:

s = "10.1.2.5.1.3.2"
? InStr(1, s, ".") + 1
4

s = "10.1"
? InStr(1, s, ".") + 1
4

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
would this work?

s = 10.1.2.5
InStr(1, s, ".") + 1

--


Gary


"Tom Ogilvy" wrote in message
...
You approach sounds like the long way around the block.

s = 10.1.2.5
level = len(s) - len(replace(s,".","")) + 1

so
Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
cnt1 = len(checker_1)-len(replace(checker_1,".")) + 1
checker_2 = ActiveSheet.Range("A4").Value
cnt2 = len(checker_2)-len(replace(checker_2,".")) + 1
checker_3 = ActiveSheet.Range("A13").Value
cnt3 = len(checker_3)-len(replace(checker_3,".")) + 1
End Sub

Also You can't refer to variables using something like name & counter
or
"Checker_" & counter

You would need array for that


Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String

Dim cnt(1 to 3) as Long


Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
cnt(1) = len(checker_1)-len(replace(checker_1,".")) + 1
checker_2 = ActiveSheet.Range("A4").Value
cnt(2) = len(checker_2)-len(replace(checker_2,".")) + 1
checker_3 = ActiveSheet.Range("A13").Value
cnt(3) = len(checker_3)-len(replace(checker_3,".")) + 1

for i = lbound(cnt) to ubound(cnt)
msgbox "Checker_" & i & ": " & cnt(i)
Next

End Sub

--
Regards,
Tom Ogilvy

"madeleine" wrote:

Hi I'm hoping that someone out there can help me. Currently I'm just
trying to find out if there is a simple way to count the number of
elements in an array, I was really hoping there would be a
myarray.count property, or a sizeof type function that would return the
number of elements. But I can't seem to find any way of doing this in
the joy of the VBA help documentation.

What I'm trying to do is get a task id e.g. 1, or 1.1, or 10.1.2 and
based upon what level I want to drill down to either analyse the task
data or not. To do this I thought I'd stick the task id into an array,
split it on the "." and then count how many elements are in the array
to determine what level the task is at e.g 1 = top level etc

Below is a sample of the tester code I've been attempting to run:

Sub tester()
Dim checker_1 As String
Dim checker_2 As String
Dim checker_3 As String
Dim counter
Dim name

name = "checker_"
Worksheets("Project Export").Activate

checker_1 = ActiveSheet.Range("A3").Value
checker_2 = ActiveSheet.Range("A4").Value
checker_3 = ActiveSheet.Range("A13").Value

counter = 1

While counter < 3
Dim raggle_array()
raggle_array = Split(checker_name & counter, ".")
counter = counter + 1

Loop

End Sub

I just really can't work out how to get the number of elements back,
and I really think it should be simple solution that doesn't involve a
loop, but if it does involve a loop then it does involve a loop.

Apologies for sounding bitter. Any and all help would be much
appreciated, I'm new to VBA and finding it very difficult to cope with
how complex everything I seem to want to do becomes.

Thanks

Madeleine