Results 1 to 2 of 2

Need Help Modifying Code

Threaded View

  1. #1
    Registered User
    Join Date
    05-29-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    37

    Need Help Modifying Code

    The code below was provided to me by a user on the forum. It takes inputs from columns and creates combinations of them (I've also attached the file).

    It uses the .CurrentRegion function - however, I'm missing information in column B, so if I start in column A, it'll ignore C and D.

    How would I modify the code such that it ignores the column with missing information and skips directly to the next column?

    Thank you!!


    Option Explicit
    
    Sub ChineseMenu()
        ' shg 2012
        ' Choose one from col A, one from col B, ...
        
        Dim wks         As Worksheet
        Dim avInp       As Variant  ' ragged input list
        Dim nCol        As Long     ' # columns in list
        Dim rOut        As Range    ' output range
        Dim iCol        As Long     ' column index
        Dim aiCum()     As Long     ' *** count of arrangements from right to left
        Dim aiCnt()     As Long     ' count of items in each column
        Dim iArr        As Long     ' arrangement number
    
        Set wks = ActiveSheet
        
        With wks.Range("A1").CurrentRegion
            .Style = "Input"
            avInp = .Value
            nCol = .Columns.Count
            Set rOut = .Resize(1).Offset(.Rows.Count + 1)
            .Offset(.Rows.Count).Resize(Rows.Count - .Rows.Count).Clear
        End With
        
        ReDim aiCum(1 To nCol + 1)
        ReDim aiCnt(1 To nCol)
        aiCum(nCol + 1) = 1
    
        With WorksheetFunction
            For iCol = nCol To 1 Step -1
                aiCnt(iCol) = .CountA(.Index(avInp, 0, iCol))
                aiCum(iCol) = aiCnt(iCol) * aiCum(iCol + 1)
            Next iCol
        End With
    
        For iArr = 1 To aiCum(1)
            For iCol = 1 To nCol
                rOut(iArr, iCol) = avInp((Int((iArr - 1) * aiCnt(iCol) / aiCum(iCol))) Mod aiCnt(iCol) + 1, iCol)
            Next iCol
        Next iArr
        
        rOut.Resize(aiCum(1)).Style = "Code"
    End Sub
    Chinese Menu.xlsm
    Last edited by ayvee0; 06-06-2013 at 09:10 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1