【求助：投行面试时遇到一个关于VBA的难题】

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

现有5个球 (A, B, C, D, E), 排成一直线的话, 总共有 5!= 120 个不同组合。请问如何把这120个组合, 以VBA运算后, 分别列出到worksheet上。

例如:

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：投行面试 VBA worksheet Works sheet 如何

我试着编了个程序，在EXCEL中运行了一下，看来还行。而且如果把第3行NOfLetters=其他数（例如7）也行
Sub ListABCDE()
Dim NOfLetters As Long, TotalNum As Long, i As Long, j As Long, n As Long, sTemp As String
NOfLetters = 5 'can change to any integer number >2 (cannot very large, as TotalNum will be too large)
TotalNum = WorksheetFunction.Fact(NOfLetters) 'i.e. 5!=120
Dim S() As String
ReDim S(2 To NOfLetters, 0 To TotalNum - 1)
S(2, 0) = "AB": S(2, 1) = "BA"
For n = 3 To NOfLetters
For i = 0 To WorksheetFunction.Fact(n - 1) - 1
    S(n, i * n) = Chr(64 + n) & S(n - 1, i)  'add one char, chr(64+n)=nth char/letter, e.g. chr(64+3)="C"
    'then begin to swap the added char with each of the existing char, one by one to create a new S(n, xxx)
    For j = 1 To n - 1
    sTemp = Replace(S(n, i * n), Chr(64 + n), "$")
    sTemp = Replace(sTemp, Chr(64 + j), Chr(64 + n))
    S(n, i * n + j) = Replace(sTemp, "$", Chr(64 + j))
    Next j
Next i
Next n
'then print to the worksheet
For i = 1 To TotalNum
For n = 1 To NOfLetters
Sheets("Sheet1").Cells(i, n).Value = Mid(S(NOfLetters, i - 1), n, 1)
Next n
Next i
End Sub

TimeT 发表于 2013-12-28 22:15
我试着编了个程序，在EXCEL中运行了一下，看来还行。而且如果把第3行NOfLetters=其他数（例如7）也行
Sub  ...

Thanks for suggestion. Here is my code. It also works.


Sub ABCDE()
Dim x, y As Variant
    y = "ABCDE"
    x = ""
Call GetPermutation(x, y)
For i = 1 To Application.WorksheetFunction.Fact(Len(y))
    For j = 1 To Len(y)
    Cells(i, Len(y) + 1 - j) = Mid(Cells(i, 1), Len(y) + 1 - j, 1)
    Next j
Next i
End Sub


Sub GetPermutation(ByVal x, ByVal y)
Dim i, j, CurrentRow As Integer
CurrentRow = Application.WorksheetFunction.CountA(Columns(1))
    j = Len(y)
    If j < 2 Then
        Cells(CurrentRow + 1, 1) = x & y
    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

牛叉