Hi all

Is it possible to decrease an array?

EG with the following Code. If I wanted to be left with the 3 highest number
in that array. I could add them to a Collection and remove them based on a
logic comparison, as 1 option. What are the other options, and are there
better watys of doing this?

Sub TestArrays()
Dim iDiceRoll(5) As Integer
Dim i As Integer

For i = 0 To 5
iDiceRoll(i) = ((6 * Rnd) + 1)
Next i
End Sub


Thanks in advance
Steve

Sub TestArrays()
Dim iDiceRoll As Variant
Dim i As Integer
Randomize
ReDim iDiceRoll(0 To 5)
For i = 0 To 5
iDiceRoll(i) = Int((6 * Rnd) + 1)
Next i
iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
For i = LBound(iDiceRoll) To UBound(iDiceRoll)
Debug.Print i, iDiceRoll(i)
Next
End Sub

iDiceRoll will have a lower bound of 1 after the use of Large.

--
Regards,
Tom Ogilvy


"steve_doc" wrote:

> Hi all
>
> Is it possible to decrease an array?
>
> EG with the following Code. If I wanted to be left with the 3 highest number
> in that array. I could add them to a Collection and remove them based on a
> logic comparison, as 1 option. What are the other options, and are there
> better watys of doing this?
>
> Sub TestArrays()
> Dim iDiceRoll(5) As Integer
> Dim i As Integer
>
> For i = 0 To 5
> iDiceRoll(i) = ((6 * Rnd) + 1)
> Next i
> End Sub
>
>
> Thanks in advance
> Steve

Thanks Tom
Works a charm
Any chance on an explanation on the code

Steve

"Tom Ogilvy" wrote:

> Sub TestArrays()
> Dim iDiceRoll As Variant
> Dim i As Integer
> Randomize
> ReDim iDiceRoll(0 To 5)
> For i = 0 To 5
> iDiceRoll(i) = Int((6 * Rnd) + 1)
> Next i
> iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> Debug.Print i, iDiceRoll(i)
> Next
> End Sub
>
> iDiceRoll will have a lower bound of 1 after the use of Large.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "steve_doc" wrote:
>
> > Hi all
> >
> > Is it possible to decrease an array?
> >
> > EG with the following Code. If I wanted to be left with the 3 highest number
> > in that array. I could add them to a Collection and remove them based on a
> > logic comparison, as 1 option. What are the other options, and are there
> > better watys of doing this?
> >
> > Sub TestArrays()
> > Dim iDiceRoll(5) As Integer
> > Dim i As Integer
> >
> > For i = 0 To 5
> > iDiceRoll(i) = ((6 * Rnd) + 1)
> > Next i
> > End Sub
> >
> >
> > Thanks in advance
> > Steve

I gave you a nice long detailed explanation, but when I hit post, this great
communities web page said page not available and it was all lost. I am not
up for reentering it. So here is the abbreviated

put this in a cell

=LARGE({2,4,6,8,10,12},{1,2,3})

after entering, go to the formula bar and select the formula. Hit F9 to
evaluate it.

You will see what it returns.

hit escape to restore the formula.

See help on the Large Worksheet function in Excel.

--
Regards,
Tom Ogilvy


"steve_doc" wrote:

> Thanks Tom
> Works a charm
> Any chance on an explanation on the code
>
> Steve
>
> "Tom Ogilvy" wrote:
>
> > Sub TestArrays()
> > Dim iDiceRoll As Variant
> > Dim i As Integer
> > Randomize
> > ReDim iDiceRoll(0 To 5)
> > For i = 0 To 5
> > iDiceRoll(i) = Int((6 * Rnd) + 1)
> > Next i
> > iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> > For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> > Debug.Print i, iDiceRoll(i)
> > Next
> > End Sub
> >
> > iDiceRoll will have a lower bound of 1 after the use of Large.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "steve_doc" wrote:
> >
> > > Hi all
> > >
> > > Is it possible to decrease an array?
> > >
> > > EG with the following Code. If I wanted to be left with the 3 highest number
> > > in that array. I could add them to a Collection and remove them based on a
> > > logic comparison, as 1 option. What are the other options, and are there
> > > better watys of doing this?
> > >
> > > Sub TestArrays()
> > > Dim iDiceRoll(5) As Integer
> > > Dim i As Integer
> > >
> > > For i = 0 To 5
> > > iDiceRoll(i) = ((6 * Rnd) + 1)
> > > Next i
> > > End Sub
> > >
> > >
> > > Thanks in advance
> > > Steve

thanks again Tom

"Tom Ogilvy" wrote:

> I gave you a nice long detailed explanation, but when I hit post, this great
> communities web page said page not available and it was all lost. I am not
> up for reentering it. So here is the abbreviated
>
> put this in a cell
>
> =LARGE({2,4,6,8,10,12},{1,2,3})
>
> after entering, go to the formula bar and select the formula. Hit F9 to
> evaluate it.
>
> You will see what it returns.
>
> hit escape to restore the formula.
>
> See help on the Large Worksheet function in Excel.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "steve_doc" wrote:
>
> > Thanks Tom
> > Works a charm
> > Any chance on an explanation on the code
> >
> > Steve
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub TestArrays()
> > > Dim iDiceRoll As Variant
> > > Dim i As Integer
> > > Randomize
> > > ReDim iDiceRoll(0 To 5)
> > > For i = 0 To 5
> > > iDiceRoll(i) = Int((6 * Rnd) + 1)
> > > Next i
> > > iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> > > For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> > > Debug.Print i, iDiceRoll(i)
> > > Next
> > > End Sub
> > >
> > > iDiceRoll will have a lower bound of 1 after the use of Large.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "steve_doc" wrote:
> > >
> > > > Hi all
> > > >
> > > > Is it possible to decrease an array?
> > > >
> > > > EG with the following Code. If I wanted to be left with the 3 highest number
> > > > in that array. I could add them to a Collection and remove them based on a
> > > > logic comparison, as 1 option. What are the other options, and are there
> > > > better watys of doing this?
> > > >
> > > > Sub TestArrays()
> > > > Dim iDiceRoll(5) As Integer
> > > > Dim i As Integer
> > > >
> > > > For i = 0 To 5
> > > > iDiceRoll(i) = ((6 * Rnd) + 1)
> > > > Next i
> > > > End Sub
> > > >
> > > >
> > > > Thanks in advance
> > > > Steve

Just to add to Tom's abbreviated notes.

Instead of putting that formula in one cell, select a1:C1 and enter the formula
in A1, but hit ctrl-shift-enter to see what's returned in all three cells.



Tom Ogilvy wrote:
>
> I gave you a nice long detailed explanation, but when I hit post, this great
> communities web page said page not available and it was all lost. I am not
> up for reentering it. So here is the abbreviated
>
> put this in a cell
>
> =LARGE({2,4,6,8,10,12},{1,2,3})
>
> after entering, go to the formula bar and select the formula. Hit F9 to
> evaluate it.
>
> You will see what it returns.
>
> hit escape to restore the formula.
>
> See help on the Large Worksheet function in Excel.
>
> --
> Regards,
> Tom Ogilvy
>
> "steve_doc" wrote:
>
> > Thanks Tom
> > Works a charm
> > Any chance on an explanation on the code
> >
> > Steve
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub TestArrays()
> > > Dim iDiceRoll As Variant
> > > Dim i As Integer
> > > Randomize
> > > ReDim iDiceRoll(0 To 5)
> > > For i = 0 To 5
> > > iDiceRoll(i) = Int((6 * Rnd) + 1)
> > > Next i
> > > iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> > > For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> > > Debug.Print i, iDiceRoll(i)
> > > Next
> > > End Sub
> > >
> > > iDiceRoll will have a lower bound of 1 after the use of Large.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "steve_doc" wrote:
> > >
> > > > Hi all
> > > >
> > > > Is it possible to decrease an array?
> > > >
> > > > EG with the following Code. If I wanted to be left with the 3 highest number
> > > > in that array. I could add them to a Collection and remove them based on a
> > > > logic comparison, as 1 option. What are the other options, and are there
> > > > better watys of doing this?
> > > >
> > > > Sub TestArrays()
> > > > Dim iDiceRoll(5) As Integer
> > > > Dim i As Integer
> > > >
> > > > For i = 0 To 5
> > > > iDiceRoll(i) = ((6 * Rnd) + 1)
> > > > Next i
> > > > End Sub
> > > >
> > > >
> > > > Thanks in advance
> > > > Steve

--

Dave Peterson

Just a thought,

But then you don't see the results as an array -- at least not as literally.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

> Just to add to Tom's abbreviated notes.
>
> Instead of putting that formula in one cell, select a1:C1 and enter the formula
> in A1, but hit ctrl-shift-enter to see what's returned in all three cells.
>
>
>
> Tom Ogilvy wrote:
> >
> > I gave you a nice long detailed explanation, but when I hit post, this great
> > communities web page said page not available and it was all lost. I am not
> > up for reentering it. So here is the abbreviated
> >
> > put this in a cell
> >
> > =LARGE({2,4,6,8,10,12},{1,2,3})
> >
> > after entering, go to the formula bar and select the formula. Hit F9 to
> > evaluate it.
> >
> > You will see what it returns.
> >
> > hit escape to restore the formula.
> >
> > See help on the Large Worksheet function in Excel.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "steve_doc" wrote:
> >
> > > Thanks Tom
> > > Works a charm
> > > Any chance on an explanation on the code
> > >
> > > Steve
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Sub TestArrays()
> > > > Dim iDiceRoll As Variant
> > > > Dim i As Integer
> > > > Randomize
> > > > ReDim iDiceRoll(0 To 5)
> > > > For i = 0 To 5
> > > > iDiceRoll(i) = Int((6 * Rnd) + 1)
> > > > Next i
> > > > iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> > > > For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> > > > Debug.Print i, iDiceRoll(i)
> > > > Next
> > > > End Sub
> > > >
> > > > iDiceRoll will have a lower bound of 1 after the use of Large.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "steve_doc" wrote:
> > > >
> > > > > Hi all
> > > > >
> > > > > Is it possible to decrease an array?
> > > > >
> > > > > EG with the following Code. If I wanted to be left with the 3 highest number
> > > > > in that array. I could add them to a Collection and remove them based on a
> > > > > logic comparison, as 1 option. What are the other options, and are there
> > > > > better watys of doing this?
> > > > >
> > > > > Sub TestArrays()
> > > > > Dim iDiceRoll(5) As Integer
> > > > > Dim i As Integer
> > > > >
> > > > > For i = 0 To 5
> > > > > iDiceRoll(i) = ((6 * Rnd) + 1)
> > > > > Next i
> > > > > End Sub
> > > > >
> > > > >
> > > > > Thanks in advance
> > > > > Steve
>
> --
>
> Dave Peterson
>

There's always a fly in any ointment. <g>

Tom Ogilvy wrote:
>
> Just a thought,
>
> But then you don't see the results as an array -- at least not as literally.
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave Peterson" wrote:
>
> > Just to add to Tom's abbreviated notes.
> >
> > Instead of putting that formula in one cell, select a1:C1 and enter the formula
> > in A1, but hit ctrl-shift-enter to see what's returned in all three cells.
> >
> >
> >
> > Tom Ogilvy wrote:
> > >
> > > I gave you a nice long detailed explanation, but when I hit post, this great
> > > communities web page said page not available and it was all lost. I am not
> > > up for reentering it. So here is the abbreviated
> > >
> > > put this in a cell
> > >
> > > =LARGE({2,4,6,8,10,12},{1,2,3})
> > >
> > > after entering, go to the formula bar and select the formula. Hit F9 to
> > > evaluate it.
> > >
> > > You will see what it returns.
> > >
> > > hit escape to restore the formula.
> > >
> > > See help on the Large Worksheet function in Excel.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "steve_doc" wrote:
> > >
> > > > Thanks Tom
> > > > Works a charm
> > > > Any chance on an explanation on the code
> > > >
> > > > Steve
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Sub TestArrays()
> > > > > Dim iDiceRoll As Variant
> > > > > Dim i As Integer
> > > > > Randomize
> > > > > ReDim iDiceRoll(0 To 5)
> > > > > For i = 0 To 5
> > > > > iDiceRoll(i) = Int((6 * Rnd) + 1)
> > > > > Next i
> > > > > iDiceRoll = Application.Large(iDiceRoll, Array(1, 2, 3))
> > > > > For i = LBound(iDiceRoll) To UBound(iDiceRoll)
> > > > > Debug.Print i, iDiceRoll(i)
> > > > > Next
> > > > > End Sub
> > > > >
> > > > > iDiceRoll will have a lower bound of 1 after the use of Large.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "steve_doc" wrote:
> > > > >
> > > > > > Hi all
> > > > > >
> > > > > > Is it possible to decrease an array?
> > > > > >
> > > > > > EG with the following Code. If I wanted to be left with the 3 highest number
> > > > > > in that array. I could add them to a Collection and remove them based on a
> > > > > > logic comparison, as 1 option. What are the other options, and are there
> > > > > > better watys of doing this?
> > > > > >
> > > > > > Sub TestArrays()
> > > > > > Dim iDiceRoll(5) As Integer
> > > > > > Dim i As Integer
> > > > > >
> > > > > > For i = 0 To 5
> > > > > > iDiceRoll(i) = ((6 * Rnd) + 1)
> > > > > > Next i
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > Thanks in advance
> > > > > > Steve
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson