What is the formula to arrange a set of numbers by a specific number.

Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9.
I would like to arrange them by (3) every third number.

The answer would be:
3, 6, 9, 4, 8, 5, 2, 7, and then 1.

Interesting problem. I can think of a few different ways to do it, but
nothing very elegant in VBA; another language would make it easier. On the
assumption that your numbers are not necessarily consecutive sequences every
time, let's try them as character strings delimited by spaces. I'll fill a
sample starting string with consecutive numbers, but I don't know how you
should get yours.

StrFm = ""
For iv = 5 to 12
StrFm = StrFm & iv & " "
next iv

Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers
(well, mine) with a space after each one. If you want to pick every 3rd
number as you describe below, you could run through that string multiple
times, each time peeling one number from the front of the string and moving
it to the back end, picking out every third number to transfer to the target
string instead:

StrTo = ""
Count = 3
Do
ps = InStr(StrFm, " ") 'find the next space
ThisNbr = Left(StrFm, ps) 'copy the next number...
StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list
Count = Count - 1
If Count = 0 Then
StrTo = StrTo & ThisNbr 'we'll take this one
Count = 3 'start the counter over
Else
StrFm = StrFm & ThisNbr 'put it back and get another
End If
Loop While StrFm < ""

I just tested this code and it seems to generate the list according to your
logic. It's not pretty, though. Maybe a collection would be a bit more
elegant.

--- "Floyd" wrote:
What is the formula to arrange a set of numbers by a specific number?

Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them
by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7,
and then 1.

Thanks Bob but I was looking for a different answer.

If N= 30, then
something like that...

Your help would be greatly appreciated

"Bob Bridges" wrote:

Interesting problem. I can think of a few different ways to do it, but
nothing very elegant in VBA; another language would make it easier. On the
assumption that your numbers are not necessarily consecutive sequences every
time, let's try them as character strings delimited by spaces. I'll fill a
sample starting string with consecutive numbers, but I don't know how you
should get yours.

StrFm = ""
For iv = 5 to 12
StrFm = StrFm & iv & " "
next iv

Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers
(well, mine) with a space after each one. If you want to pick every 3rd
number as you describe below, you could run through that string multiple
times, each time peeling one number from the front of the string and moving
it to the back end, picking out every third number to transfer to the target
string instead:

StrTo = ""
Count = 3
Do
ps = InStr(StrFm, " ") 'find the next space
ThisNbr = Left(StrFm, ps) 'copy the next number...
StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list
Count = Count - 1
If Count = 0 Then
StrTo = StrTo & ThisNbr 'we'll take this one
Count = 3 'start the counter over
Else
StrFm = StrFm & ThisNbr 'put it back and get another
End If
Loop While StrFm < ""

I just tested this code and it seems to generate the list according to your
logic. It's not pretty, though. Maybe a collection would be a bit more
elegant.

--- "Floyd" wrote:
What is the formula to arrange a set of numbers by a specific number?

Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them
by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7,
and then 1.

Not sure what you mean. The below algorithm does indeed
arrange a set of numbers, choosing every third one, and it
seems to match the example you gave. Do you mean you
wanted to know how to do it to a sequence of numbers from
1 to 30? The below code works for that, after you modify the
initial startup loop to match your specs. Do you mean you
want to be able to pick every 30th number, not just every 3rd?
But the below logic does that too, if only you modify the way
Count is handled. What 'N' is supposed to be set to 3?

--- "Floyd" wrote:
Thanks Bob but I was looking for a different answer. If N= 30, then
something like that... Your help would be greatly appreciated

--- "Bob Bridges" wrote:
Interesting problem. I can think of a few different ways to do it,
but nothing very elegant in VBA; another language would make
it easier. On the assumption that your numbers are not
necessarily consecutive sequences every time, let's try them as
character strings delimited by spaces. I'll fill a sample starting
string with consecutive numbers, but I don't know how you
should get yours.

StrFm = ""
For iv = 5 to 12
StrFm = StrFm & iv & " "
next iv

Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers
(well, mine) with a space after each one. If you want to pick every 3rd
number as you describe below, you could run through that string multiple
times, each time peeling one number from the front of the string and moving
it to the back end, picking out every third number to transfer to the target
string instead:

StrTo = ""
Count = 3
Do
ps = InStr(StrFm, " ") 'find the next space
ThisNbr = Left(StrFm, ps) 'copy the next number...
StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list
Count = Count - 1
If Count = 0 Then
StrTo = StrTo & ThisNbr 'we'll take this one
Count = 3 'start the counter over
Else
StrFm = StrFm & ThisNbr 'put it back and get another
End If
Loop While StrFm < ""

--- "Floyd" wrote:
What is the formula to arrange a set of numbers by a specific number?

Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them
by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7,
and then 1.

On Sep 17, 12:18*am, Floyd wrote:
What is the formula to arrange a set of numbers by a specific number. *

Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9.
I would like to arrange them by (3) every third number.

The answer would be:
3, 6, 9, 4, 8, 5, 2, 7, and then 1.

I'm not quite sure what you mean by "formula". What you are asking
about is related to the classic Josephus problem
http://en.wikipedia.org/wiki/Josephus_problem (which asks for the last
number listed) and even that is a fairly hard problem - so I don't
think a closed form solution which gives the full listing directly
without needing to actually step through the process is possible -
though I may be wrong there. Maybe you could post this in sci.math.

In any event, I wrote a VBA function which takes an array and a step
size and returns the array obtained by repeatedly stepping through the
remaining items according to the step size. I also include two test
routines (the first one to verify output, the second one a rough speed
test):

Function JosephusPermutation(A As Variant, stepSize As Long) As
Variant
Dim lower As Long, upper As Long
Dim i As Long, j As Long
Dim whereAt As Long, fromWhere As Long
Dim tempA As Variant, retA As Variant

lower = LBound(A)
upper = UBound(A)
ReDim tempA(lower To upper, 0 To 1)
ReDim retA(lower To upper)
For i = lower To upper
tempA(i, 0) = A(i)
tempA(i, 1) = i + 1
Next i
tempA(upper, 1) = lower
whereAt = lower
For i = lower To upper
For j = 1 To stepSize - 1 'walk
fromWhere = whereAt
whereAt = tempA(whereAt, 1)
Next j
retA(i) = tempA(whereAt, 0)
whereAt = tempA(whereAt, 1)
tempA(fromWhere, 1) = whereAt 'effectively removing item just
selected
Next i
JosephusPermutation = retA
End Function

Sub Test()
Dim A As Variant, skip As Long
Dim i As Long
Dim retString As String
A = Split(InputBox("Enter numbers, separated by spaces"))
skip = InputBox("Enter step size")
A = JosephusPermutation(A, skip)
For i = 0 To UBound(A)
retString = retString & " " & A(i)
Next i
retString = "Output:" & retString
MsgBox retString
End Sub

Sub Test2()
Dim A(1 To 10000) As Integer
Dim i As Long
For i = 1 To 10000
A(i) = i
Next i
Range("A1:A10000").Value =
Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179))

End Sub


hth

-scattered

Thank You Bob and Scattered for your response but I looking for something
different. Maybe the information below is helpful.

I posted this question about two year ago and received a different answer.
The answer that I received had something to do with the following;
{=everynth(20, 3)}
This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA
is listed below:

Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
x(i) = i
Next i
ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function

This is the Macro that I want to use. Please review, it's just not working
for me.

"scattered" wrote:

On Sep 17, 12:18 am, Floyd wrote:
What is the formula to arrange a set of numbers by a specific number.

Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9.
I would like to arrange them by (3) every third number.

The answer would be:
3, 6, 9, 4, 8, 5, 2, 7, and then 1.

I'm not quite sure what you mean by "formula". What you are asking
about is related to the classic Josephus problem
http://en.wikipedia.org/wiki/Josephus_problem (which asks for the last
number listed) and even that is a fairly hard problem - so I don't
think a closed form solution which gives the full listing directly
without needing to actually step through the process is possible -
though I may be wrong there. Maybe you could post this in sci.math.

In any event, I wrote a VBA function which takes an array and a step
size and returns the array obtained by repeatedly stepping through the
remaining items according to the step size. I also include two test
routines (the first one to verify output, the second one a rough speed
test):

Function JosephusPermutation(A As Variant, stepSize As Long) As
Variant
Dim lower As Long, upper As Long
Dim i As Long, j As Long
Dim whereAt As Long, fromWhere As Long
Dim tempA As Variant, retA As Variant

lower = LBound(A)
upper = UBound(A)
ReDim tempA(lower To upper, 0 To 1)
ReDim retA(lower To upper)
For i = lower To upper
tempA(i, 0) = A(i)
tempA(i, 1) = i + 1
Next i
tempA(upper, 1) = lower
whereAt = lower
For i = lower To upper
For j = 1 To stepSize - 1 'walk
fromWhere = whereAt
whereAt = tempA(whereAt, 1)
Next j
retA(i) = tempA(whereAt, 0)
whereAt = tempA(whereAt, 1)
tempA(fromWhere, 1) = whereAt 'effectively removing item just
selected
Next i
JosephusPermutation = retA
End Function

Sub Test()
Dim A As Variant, skip As Long
Dim i As Long
Dim retString As String
A = Split(InputBox("Enter numbers, separated by spaces"))
skip = InputBox("Enter step size")
A = JosephusPermutation(A, skip)
For i = 0 To UBound(A)
retString = retString & " " & A(i)
Next i
retString = "Output:" & retString
MsgBox retString
End Sub

Sub Test2()
Dim A(1 To 10000) As Integer
Dim i As Long
For i = 1 To 10000
A(i) = i
Next i
Range("A1:A10000").Value =
Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179))

End Sub


hth

-scattered

On Sep 18, 4:32*pm, Floyd wrote:
Thank You Bob and Scattered for your response but I looking for something
different. *Maybe the information below is helpful.

I posted this question about two year ago and received a different answer.. *
The answer that I received had something to do with the following;
{=everynth(20, 3)}
This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA
is listed below:

Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
* *x(i) = i
Next i
ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function

This is the Macro that I want to use. *Please review, it's just not working
for me.



"scattered" wrote:
On Sep 17, 12:18 am, Floyd wrote:
What is the formula to arrange a set of numbers by a specific number. *

Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9.
I would like to arrange them by (3) every third number.

The answer would be:
3, 6, 9, 4, 8, 5, 2, 7, and then 1.

I'm not quite sure what you mean by "formula". What you are asking
about is related to the classic Josephus problem
http://en.wikipedia.org/wiki/Josephus_problem(which asks for the last
number listed) and even that is a fairly hard problem - so I don't
think a closed form solution which gives the full listing directly
without needing to actually step through the process is possible -
though I may be wrong there. Maybe you could post this in sci.math.

In any event, I wrote a VBA function which takes an array and a step
size and returns the array obtained by repeatedly stepping through the
remaining items according to the step size. I also include two test
routines (the first one to verify output, the second one a rough speed
test):

Function JosephusPermutation(A As Variant, stepSize As Long) As
Variant
* * Dim lower As Long, upper As Long
* * Dim i As Long, j As Long
* * Dim whereAt As Long, fromWhere As Long
* * Dim tempA As Variant, retA As Variant

* * lower = LBound(A)
* * upper = UBound(A)
* * ReDim tempA(lower To upper, 0 To 1)
* * ReDim retA(lower To upper)
* * For i = lower To upper
* * * * tempA(i, 0) = A(i)
* * * * tempA(i, 1) = i + 1
* * Next i
* * tempA(upper, 1) = lower
* * whereAt = lower
* * For i = lower To upper
* * * * For j = 1 To stepSize - 1 'walk
* * * * * * fromWhere = whereAt
* * * * * * whereAt = tempA(whereAt, 1)
* * * * Next j
* * * * retA(i) = tempA(whereAt, 0)
* * * * whereAt = tempA(whereAt, 1)
* * * * tempA(fromWhere, 1) = whereAt 'effectively removing item just
selected
* * Next i
* * JosephusPermutation = retA
End Function

Sub Test()
* * Dim A As Variant, skip As Long
* * Dim i As Long
* * Dim retString As String
* * A = Split(InputBox("Enter numbers, separated by spaces"))
* * skip = InputBox("Enter step size")
* * A = JosephusPermutation(A, skip)
* * For i = 0 To UBound(A)
* * * * retString = retString & " " & A(i)
* * Next i
* * retString = "Output:" & retString
* * MsgBox retString
End Sub

Sub Test2()
* * Dim A(1 To 10000) As Integer
* * Dim i As Long
* * For i = 1 To 10000
* * * * A(i) = i
* * Next i
* * Range("A1:A10000").Value =
Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179))

End Sub

hth

-scattered- Hide quoted text -

- Show quoted text -

Your problem is *using* an already written macro by array formulas.
Why didn't you say so? In any event: try replacing

{=everynth(20, 3)}

by

{=transpose(everynth(20, 3))}


hth

-scattered

Well, it looks a bit more complicated than necessary. Using arrays probably
isn't the VERY simplest way to do it anyway, but if you want to use arrays
for some reason I would do it this way:

Function EveryNth(Top As Long, Nth As Long)
Dim Fm(1 to Top) as Boolean 'Tracks which have been transferred
Dim To(1 to Top) as Long 'Numbers in new order (output of fnc)
Dim nTaken as Long 'Count of numbers tranferred to new
Dim N as Long 'Temp counter of Nth

' Initialize
For ii = 1 to Top
Fm(ii) = True
End If
iTo=0
nTaken = Top
N = Nth

' Loop until all numbers transferred to new list
Do
For iFm = 1 To Top
If Fm(iFm) Then 'this number not tranferred yet
N = N - 1 '...but we're transferring only every Nth
If N < 1 Then
iTo = iTo + 1
To(iTo) = iFm
Fm(iFm) = False 'mark as transferred
nTaken = nTaken - 1 'one down, nTaken to go
N = Nth 'restart the temp counter of Nth
End If
End If
Next iFm
Loop While nTaken 0 'keep going until all taken

End Function

Only slightly shorter, but it uses one fewer counter, I think, and the
counters are named for greater clarity.

But if want to keep on using the one you have, I think you'll have to tell
us what's "not working" about it. Is it bombing in the middle with an error
(and if so what error)? pretending to work but giving incorrect results (and
if so what results)?

Better yet, have you tried walking through its execution one step at a time,
using <F8 and the other debug tools? If you've never tried them before,
they're well worth learning; a little familiarity with them might well have
saved you these days of asking on the forum. If you're not sure how they
work, let us know and someone (me! pick me!) will help you get started.

--- "Floyd" wrote:
Thank You Bob and Scattered for your response but I looking for something
different. Maybe the information below is helpful.

I posted this question about two year ago and received a different answer.
The answer that I received had something to do with {=everynth(20, 3)}.
This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA
is listed below:

Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
x(i) = i
Next i
ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function

This is the Macro that I want to use. Please review, it's just not working
for me.

Thank You Bob,

Please, help me in learning the new tools so that I can solve my problem.

"Bob Bridges" wrote:

Well, it looks a bit more complicated than necessary. Using arrays probably
isn't the VERY simplest way to do it anyway, but if you want to use arrays
for some reason I would do it this way:

Function EveryNth(Top As Long, Nth As Long)
Dim Fm(1 to Top) as Boolean 'Tracks which have been transferred
Dim To(1 to Top) as Long 'Numbers in new order (output of fnc)
Dim nTaken as Long 'Count of numbers tranferred to new
Dim N as Long 'Temp counter of Nth

' Initialize
For ii = 1 to Top
Fm(ii) = True
End If
iTo=0
nTaken = Top
N = Nth

' Loop until all numbers transferred to new list
Do
For iFm = 1 To Top
If Fm(iFm) Then 'this number not tranferred yet
N = N - 1 '...but we're transferring only every Nth
If N < 1 Then
iTo = iTo + 1
To(iTo) = iFm
Fm(iFm) = False 'mark as transferred
nTaken = nTaken - 1 'one down, nTaken to go
N = Nth 'restart the temp counter of Nth
End If
End If
Next iFm
Loop While nTaken 0 'keep going until all taken

End Function

Only slightly shorter, but it uses one fewer counter, I think, and the
counters are named for greater clarity.

But if want to keep on using the one you have, I think you'll have to tell
us what's "not working" about it. Is it bombing in the middle with an error
(and if so what error)? pretending to work but giving incorrect results (and
if so what results)?

Better yet, have you tried walking through its execution one step at a time,
using <F8 and the other debug tools? If you've never tried them before,
they're well worth learning; a little familiarity with them might well have
saved you these days of asking on the forum. If you're not sure how they
work, let us know and someone (me! pick me!) will help you get started.

--- "Floyd" wrote:
Thank You Bob and Scattered for your response but I looking for something
different. Maybe the information below is helpful.

I posted this question about two year ago and received a different answer.
The answer that I received had something to do with {=everynth(20, 3)}.
This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA
is listed below:

Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
x(i) = i
Next i
ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function

This is the Macro that I want to use. Please review, it's just not working
for me.

Well, start at the beginning, though I've little doubt you've seen the first
step: You have to get to the Visual Basic Editor, where this code is typed
in.
You get there from your spreadsheet by selecting Tools, Macros, Visual Basic
Editor. Your actual code can be in a number of places there, but actually
I'm
sure you must already know about this part because after all how else could
you
have captured the code and copied it to us?

So now you're looking at your code and you want to know what's going wrong
with
it. I expect you inherited this code from someone else, rather than having
written it yourself, but you can still figure out what's going on if you can
watch the program run, step by step, seeing inside the program so to speak
so you
can tell what the statement it's about to execute and what is the value of
each
variable at any given time. And so you can: Use your arrow keys to bring
the
text cursor down until it's somewhere on the first line, the one that says
"Function EveryNth(etc)". With your text-insertion bar somewhere on that
line,
hit <F9.

Immediately that whole line is highlighted (probably in the shade of dark red
that I think of as burgandy), and a bugandy dot appears in the margin
immediately
to the left. In the language of VBA you have just set a "breakpoint" in your
program: When the program starts executing and gets to that point, it'll
stop
and invite you to look over the program, investigate current values, type
immediate commands for execution, even change some of the statements, and
then
tell it to resume execution. It's pretty powerful stuff.

You can set breakpoints on pretty much any executable statement in your
program,
either by using <F9 or by just clicking with your mouse in the left margin
next
to your line. If you hit <F9 or click again on the same line, the
breakpoint
will go away.

Now watch what happens when you execute the program. By the way, how DO you
execute the program? I mean, do you go to Tools, Macros and pick a macro
from
the list to execute? Do you hit a button? Or is it, in this case, a
worksheet
function? If the latter, the way I always get it to execute (there may be
others) is just go back to the spreadsheet, to one of the cells that uses
that
function; there hit <F2 to start editing the formula in that cell, and then
hit
<Enter without changing anything.

Immediately, as I recall, the view will switch from the spreadsheet to the
VBE,
with the breakpointed line now highlighted in yellow, indicating that this
statement will be next to execute when you allow it. Since you put the
breakpoint on the Sub line, it hasn't done a blessed thing yet. That means
you're looking at in before it's done anything wrong, but that in turn means
you
haven't seen anything useful yet. Here's what you do: Hit <F8.

<F8 allows the program to execute exactly one command or statement, in this
case
the Function "command" itself. That doesn't do anything very interesting,
but if
you hit <F8 while you're looking at the VBE you'll see the yellow line jump
to
the next command, which is not, as you might, expect the next line ("Dim i As
Long") but ... well, actually, I'm not sure whether it'll be the ReDim or
the For
statement. At any rate it doesn't consider Dim to be an executable
statement so
it doesn't show you anything for those, just leaps over them - in fact if
you try
to set a breakpoint on a Dim statement, nothing happens because breakpoints
aren't allowed there.

At any rate, now the yellow marker has advanced one, and something
interesting
has happened. Hover your mouse cursor over any variable name in your code:
Num,
Nth, i, whatever. What you should see -- and if you don't, something's
wrong --
is a pop-up message saying, for example, "Num = 30". Most of the other
variables
will say simply "i = Empty", because you haven't assigned them any values
yet --
or come to think of it maybe they'll be set to 0, because of the Dim
statement.
But Num and Nth you'll be able to see once you've come out of the
introductory
Function statement itself.

Each time you hit <F8 it'll advance another step and you can see the
results.
You can advance one step at a time through the entire execution, if you like,
which will give you a clear picture of everything it's doing. But if that
get's
boring and you want to jump ahead, you can do either of two things:

1) Hit <F5; this just runs along and finished the execution without you.
But if
you still need to see what it's doing later -- just not right in this part
of the
program -- you can
2) Set another breakpoint, with the mouse or using <F9, and THEN hit <F5;
the
program will run along without you, just as before, but as soon as it gets to
this new breakpoint it'll stop and give you a chance to catch up again.

There are other and fancier things your can do. Look through the Debug menu
for
some of them, and in your Help file(s). But one thing for sure you'll need
to do
in this case, because you have arrays: You can't hover your mouse over x
and y
to get their value because they don't have "a value", they have more like 30,
being arrays. To see what's going on inside them you have to add them to a
"watch" window.

To do that, either bring down the Debug menu or right-click on the variable,
and
in either resulting drop-down menu select Add Watch. A little window will
pop up
allowing you to set some options; for now, just make sure the "Expression" is
equal to the variable you want to inspect and hit OK or <Enter. In the
Watch
window -- it should be just below your code window -- an entry for that
variable
shows up. If it's a scalar variable you can see its present value to the
right.
If it's an object or an array, you have to click on the + symbol to expand it
into its component parts. Either way you get a complete picture of what's
happening in this variable or object.

The Watch window in no way interferes with your ability to continue
executing the
program step by step or many at a time. These are the "tools" I had in
mind; you
can see what your program is doing, and if you know what it SHOULD be doing
then
you should be able to see at what point it goes wrong, and figure out why.

There's more, of course, but this is most of what I need for most debugging.

--- "Floyd" wrote:
Thank You Bob, Please, help me in learning the new tools so that I
can solve my problem.

--- "Bob Bridges" wrote:
....But if you want to keep on using the one you have, I think you'll have
to tell us what's "not working" about it. Is it bombing in the middle with
an error (and if so what error)? pretending to work but giving incorrect
results (and if so what results)?

Better yet, have you tried walking through its execution one step at a time,
using <F8 and the other debug tools? If you've never tried them before,
they're well worth learning; a little familiarity with them might well have
saved you these days of asking on the forum. If you're not sure how they
work, let us know and someone (me! pick me!) will help you get started.

On Sep 18, 4:58*pm, scattered wrote:
On Sep 18, 4:32*pm, Floyd wrote:





Thank You Bob and Scattered for your response but I looking for something
different. *Maybe the information below is helpful.

I posted this question about two year ago and received a different answer. *
The answer that I received had something to do with the following;
{=everynth(20, 3)}
This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA
is listed below:

Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
* *x(i) = i
Next i
ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function

This is the Macro that I want to use. *Please review, it's just not working
for me.

"scattered" wrote:
On Sep 17, 12:18 am, Floyd wrote:
What is the formula to arrange a set of numbers by a specific number. *

Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9.
I would like to arrange them by (3) every third number.

The answer would be:
3, 6, 9, 4, 8, 5, 2, 7, and then 1.

I'm not quite sure what you mean by "formula". What you are asking
about is related to the classic Josephus problem
http://en.wikipedia.org/wiki/Josephus_problem(whichasks for the last
number listed) and even that is a fairly hard problem - so I don't
think a closed form solution which gives the full listing directly
without needing to actually step through the process is possible -
though I may be wrong there. Maybe you could post this in sci.math.

In any event, I wrote a VBA function which takes an array and a step
size and returns the array obtained by repeatedly stepping through the
remaining items according to the step size. I also include two test
routines (the first one to verify output, the second one a rough speed
test):

Function JosephusPermutation(A As Variant, stepSize As Long) As
Variant
* * Dim lower As Long, upper As Long
* * Dim i As Long, j As Long
* * Dim whereAt As Long, fromWhere As Long
* * Dim tempA As Variant, retA As Variant

* * lower = LBound(A)
* * upper = UBound(A)
* * ReDim tempA(lower To upper, 0 To 1)
* * ReDim retA(lower To upper)
* * For i = lower To upper
* * * * tempA(i, 0) = A(i)
* * * * tempA(i, 1) = i + 1
* * Next i
* * tempA(upper, 1) = lower
* * whereAt = lower
* * For i = lower To upper
* * * * For j = 1 To stepSize - 1 'walk
* * * * * * fromWhere = whereAt
* * * * * * whereAt = tempA(whereAt, 1)
* * * * Next j
* * * * retA(i) = tempA(whereAt, 0)
* * * * whereAt = tempA(whereAt, 1)
* * * * tempA(fromWhere, 1) = whereAt 'effectively removing item just
selected
* * Next i
* * JosephusPermutation = retA
End Function

Sub Test()
* * Dim A As Variant, skip As Long
* * Dim i As Long
* * Dim retString As String
* * A = Split(InputBox("Enter numbers, separated by spaces"))
* * skip = InputBox("Enter step size")
* * A = JosephusPermutation(A, skip)
* * For i = 0 To UBound(A)
* * * * retString = retString & " " & A(i)
* * Next i
* * retString = "Output:" & retString
* * MsgBox retString
End Sub

Sub Test2()
* * Dim A(1 To 10000) As Integer
* * Dim i As Long
* * For i = 1 To 10000
* * * * A(i) = i
* * Next i
* * Range("A1:A10000").Value =
Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179))

End Sub

hth

-scattered- Hide quoted text -

- Show quoted text -

Your problem is *using* an already written macro by array formulas.
Why didn't you say so? In any event: try replacing

*{=everynth(20, 3)}

by

*{=transpose(everynth(20, 3))}

hth

-scattered- Hide quoted text -

- Show quoted text -

Did this not work? The macro you gave doesn't have any bugs in it that
I see and yields the same output as the one I wrote. If you highlight
a column range of 20 cells and enter =transpose(everynth(20,3)) in the
formula bar then hit Ctrl+Shift+Enter you should get the 20 numbers
you are looking for displayed in the spreadsheet. Note that *you*
don't include the { and } in the array formula
{=transpose(everynth(20,3)} - Excel provides them when you hit ctrl
+shift+enter. Also, if you don't include the transpose function then
you just get the number 3 repeated 20 times (which is what I guessed
you meant by "macro not working for me"). This is because the everynth
returns a horizontal array and columns contain vertical arrays.

Another possible problem is that you put the macro code in a sheet (or
workbook) module instead of a general code module. This is probably
the case if you are seeing #NAME? displayed on the worksheet. If so,
open up the visual basic editor and *Insert* a new module, then cut/
paste the everynth code from the sheet module to the new module.

If none of this works - just what is happening? What do you mean by
"doesn't work"?

hth

-scattered