Welcome to the home of createsline.com

To change this page, upload your website into the public_html directory

Date Created: Fri Feb 14 23:51:08 2025

Sub GenerateResult1() Dim wsSource As Worksheet Dim wsResult As Worksheet Dim fields As Variant Dim n As Long Dim lastRow As Long Dim i As Long, j As Long Dim colIndex As Long Dim useColumn As Boolean ' 定義欄位名稱 fields = Array("欄1", "欄2", "欄3", "欄4", "欄5", "欄6") n = UBound(fields) + 1 ' 總欄位數 ' 設置工作表 Set wsSource = ThisWorkbook.Sheets("Source") ' 將 Source 替換為你的工作表名稱 ' 檢查 Result1 是否存在，若存在則刪除 On Error Resume Next Application.DisplayAlerts = False ThisWorkbook.Sheets("Result1").Delete Application.DisplayAlerts = True On Error GoTo 0 ' 創建新的 Result1 工作表 Set wsResult = ThisWorkbook.Sheets.Add wsResult.Name = "Result1" ' 寫入標題行（從 B 欄開始） For i = 0 To UBound(fields) wsResult.Cells(1, i + 2).Value = fields(i) ' 從 B 欄開始 Next i wsResult.Cells(1, n + 2).Value = "用戶輸入" ' 從 B 欄開始 ' 生成所有組合 Call GenerateCombinations(fields, n, Array("", "", "", "", "", ""), wsSource, wsResult, 2) MsgBox "Result1 工作表生成完成！請在「用戶輸入」欄位中輸入數字。" End Sub Sub GenerateCombinations(fields As Variant, n As Long, currentComb As Variant, wsSource As Worksheet, wsResult As Worksheet, rowIndex As Long) Dim i As Long, j As Long Dim lastRow As Long Dim useColumn As Boolean ' 如果當前組合的長度等於 n，則輸出 If n = 0 Then For i = 0 To UBound(fields) wsResult.Cells(rowIndex, i + 2).Value = currentComb(i) ' 從 B 欄開始 Next i wsResult.Cells(rowIndex, UBound(fields) + 3).Value = 0 ' 初始化用戶輸入欄位（從 B 欄開始） rowIndex = rowIndex + 1 Exit Sub End If ' 檢查該欄位是否使用 useColumn = (wsSource.Cells(1, UBound(fields) - n + 2).Value = "Y") ' 遞歸生成組合 If useColumn Then lastRow = wsSource.Cells(wsSource.Rows.Count, UBound(fields) - n + 2).End(xlUp).Row For i = 3 To lastRow ' 從第 3 行開始讀取值 If wsSource.Cells(i, UBound(fields) - n + 2).Value <> "" Then ' 檢查值是否為空 currentComb(UBound(fields) - n + 1) = wsSource.Cells(i, UBound(fields) - n + 2).Value Call GenerateCombinations(fields, n - 1, currentComb, wsSource, wsResult, rowIndex) End If Next i Else currentComb(UBound(fields) - n + 1) = "" ' 如果欄位不使用，設為空值 Call GenerateCombinations(fields, n - 1, currentComb, wsSource, wsResult, rowIndex) End If End Sub
Sub GenerateResult1() Dim wsSource As Worksheet Dim wsResult As Worksheet Dim fields As Variant Dim n As Long Dim lastRow As Long Dim i As Long, j As Long Dim colIndex As Long Dim useColumn As Boolean ' 定義欄位名稱 fields = Array("欄1", "欄2", "欄3", "欄4", "欄5", "欄6") n = UBound(fields) + 1 ' 總欄位數 ' 設置工作表 Set wsSource = ThisWorkbook.Sheets("Source") ' 將 Source 替換為你的工作表名稱 ' 檢查 Result1 是否存在，若存在則刪除 On Error Resume Next Application.DisplayAlerts = False ThisWorkbook.Sheets("Result1").Delete Application.DisplayAlerts = True On Error GoTo 0 ' 創建新的 Result1 工作表 Set wsResult = ThisWorkbook.Sheets.Add wsResult.Name = "Result1" ' 寫入標題行（從 B 欄開始） For i = 0 To UBound(fields) wsResult.Cells(1, i + 1).Value = fields(i) ' 從 B 欄開始 Next i wsResult.Cells(1, n + 1).Value = "用戶輸入" ' 從 B 欄開始 ' 生成所有組合 Call GenerateCombinations(fields, n, Array("", "", "", "", "", ""), wsSource, wsResult, 2) MsgBox "Result1 工作表生成完成！請在「用戶輸入」欄位中輸入數字。" End Sub Sub GenerateCombinations(fields As Variant, n As Long, currentComb As Variant, wsSource As Worksheet, wsResult As Worksheet, rowIndex As Long) Dim i As Long, j As Long Dim lastRow As Long Dim useColumn As Boolean ' 如果當前組合的長度等於 n，則輸出 If n = 0 Then For i = 0 To UBound(fields) wsResult.Cells(rowIndex, i + 1).Value = currentComb(i) ' 從 B 欄開始 Next i wsResult.Cells(rowIndex, UBound(fields) + 2).Value = 0 ' 初始化用戶輸入欄位（從 B 欄開始） rowIndex = rowIndex + 1 Exit Sub End If ' 檢查該欄位是否使用 useColumn = (wsSource.Cells(1, UBound(fields) - n + 2).Value = "Y") ' 遞歸生成組合 If useColumn Then lastRow = wsSource.Cells(wsSource.Rows.Count, UBound(fields) - n + 2).End(xlUp).Row For i = 3 To lastRow ' 從第 3 行開始讀取值 If wsSource.Cells(i, UBound(fields) - n + 2).Value <> "" Then ' 檢查值是否為空 currentComb(UBound(fields) - n + 1) = wsSource.Cells(i, UBound(fields) - n + 2).Value Call GenerateCombinations(fields, n - 1, currentComb, wsSource, wsResult, rowIndex) End If Next i Else currentComb(UBound(fields) - n + 1) = "" ' 如果欄位不使用，設為空值 Call GenerateCombinations(fields, n - 1, currentComb, wsSource, wsResult, rowIndex) End If End Sub