Can someone please help me find out why this code is not working. I a
trying to write a program that reads customer orders in a worksheet
then it outputs all possible permutations of those orders in the nex
columns. The number of orders is dynamic and is represented by th
variable n. I just defined n as a number for now to see if wa
working. Here is the code and I will note below where the runtim
error occurs.

Dim CurrentCol
Dim arr() As Variant
Dim rng As Range

Sub GetString()
Dim instring As String
Dim n As Integer
Dim i As Integer
Dim j As Integer

i = 1
j = 1
n = 5

For i = 1 To n
instring = instring & i
Next i

Set rng = Range("A1").Resize(Application.CountA(Range("A1.A6 5336")))
arr = rng.Value

ActiveSheet.Columns(2).Resize(, 255).Clear
CurrentCol = 1
Call GetPermutation("", instring)

End Sub


Sub GetPermutation(x As String, y As String)

Dim i As Integer, j As Integer, sres As String
j = Len(y)
If j < 2 Then
sres = x & y
For rw = 1 To Len(x & y)
Cells(rw, CurrentCol) = arr(CLng(Mid(x & y, rw, 1))) ***
(runtime error 9 "subscript out of range" occurs here)****
Next

CurrentCol = CurrentCol + 1

Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next

End If
End Sub

Please help. Thanks

--
Message posted from http://www.ExcelForum.com

Hi
try the following:
change the line
arr = rng.Value

to
ReDim arr(rng.Rows.Count)
For i = 1 To rng.Rows.Count
arr(i) = Cells(i, "A").Value
Next


--
Regards
Frank Kabel
Frankfurt, Germany

Can someone please help me find out why this code is not working. I
am trying to write a program that reads customer orders in a
worksheet, then it outputs all possible permutations of those orders
in the next columns. The number of orders is dynamic and is
represented by the variable n. I just defined n as a number for now
to see if was working. Here is the code and I will note below where
the runtime error occurs.

Dim CurrentCol
Dim arr() As Variant
Dim rng As Range

Sub GetString()
Dim instring As String
Dim n As Integer
Dim i As Integer
Dim j As Integer

i = 1
j = 1
n = 5

For i = 1 To n
instring = instring & i
Next i

Set rng = Range("A1").Resize(Application.CountA(Range("A1.A6 5336")))
arr = rng.Value

ActiveSheet.Columns(2).Resize(, 255).Clear
CurrentCol = 1
Call GetPermutation("", instring)

End Sub


Sub GetPermutation(x As String, y As String)

Dim i As Integer, j As Integer, sres As String
j = Len(y)
If j < 2 Then
sres = x & y
For rw = 1 To Len(x & y)
Cells(rw, CurrentCol) = arr(CLng(Mid(x & y, rw, 1))) ****
(runtime error 9 "subscript out of range" occurs here)****
Next

CurrentCol = CurrentCol + 1

Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next

End If
End Sub

Please help. Thanks,


---
Message posted from http://www.ExcelForum.com/

Frank gave you a solution.

the reason you are having problems is that if you use

arr = rng.Value


then arr is a 1 by n, 1 by 1 two dimensional array and your function is
looking for a one dimensional array.
You can also do

arr = Application.Transpose(rng.Value)

which will return a 1 x n one dimensional array.

--
Regards,
Tom Ogilvy



"margie " wrote in message
...
Can someone please help me find out why this code is not working. I am
trying to write a program that reads customer orders in a worksheet,
then it outputs all possible permutations of those orders in the next
columns. The number of orders is dynamic and is represented by the
variable n. I just defined n as a number for now to see if was
working. Here is the code and I will note below where the runtime
error occurs.

Dim CurrentCol
Dim arr() As Variant
Dim rng As Range

Sub GetString()
Dim instring As String
Dim n As Integer
Dim i As Integer
Dim j As Integer

i = 1
j = 1
n = 5

For i = 1 To n
instring = instring & i
Next i

Set rng = Range("A1").Resize(Application.CountA(Range("A1.A6 5336")))
arr = rng.Value

ActiveSheet.Columns(2).Resize(, 255).Clear
CurrentCol = 1
Call GetPermutation("", instring)

End Sub


Sub GetPermutation(x As String, y As String)

Dim i As Integer, j As Integer, sres As String
j = Len(y)
If j < 2 Then
sres = x & y
For rw = 1 To Len(x & y)
Cells(rw, CurrentCol) = arr(CLng(Mid(x & y, rw, 1))) ****
(runtime error 9 "subscript out of range" occurs here)****
Next

CurrentCol = CurrentCol + 1

Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next

End If
End Sub

Please help. Thanks,


---
Message posted from http://www.ExcelForum.com/