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