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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:03CC7A1C-9C8D-4A5A-BEF8-(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> would this work?
>
> s = 10.1.2.5
> InStr(1, s, ".") + 1
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:03CC7A1C-9C8D-4A5A-BEF8-(E-Mail Removed)...
>> 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
>>>
>>>
>
>