Excel VBA开发需求：跨表随机选单元格并复制拼接

Got it, let's break down how to build this Excel VBA solution for your requirement. I'll walk you through the code and setup so you can get it working quickly.

First, double-check your Sheet2 has these elements (adjust ranges to match your actual layout if needed):

• 4 dropdown boxes (let’s assume they’re in B2:B5) linked to the column names from Sheet1
• A command button (add via Developer tab > Insert > Button (Form Control))
• A target range for pasting the random cells (e.g., D2:D5)
• A dedicated cell for the concatenated result (e.g., F2)

Open the VBA editor by pressing Alt + F11, insert a new module, and paste this code:

Sub RandomSelectAndConcatenate()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim dropDownRange As Range
    Dim targetPasteRange As Range
    Dim concatCell As Range
    Dim colName As String
    Dim sourceCol As Range
    Dim lastRow As Long
    Dim randomRow As Long
    Dim concatText As String
    
    ' Link to your worksheets (update names if yours are different)
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsTarget = ThisWorkbook.Sheets("Sheet2")
    
    ' Define your Sheet2 ranges - tweak these to match your actual layout
    Set dropDownRange = wsTarget.Range("B2:B5") ' 4 dropdown cells
    Set targetPasteRange = wsTarget.Range("D2:D5") ' Where to paste random selections
    Set concatCell = wsTarget.Range("F2") ' Where to place concatenated text
    
    concatText = ""
    
    ' Loop through each dropdown selection
    For i = 1 To dropDownRange.Cells.Count
        colName = dropDownRange.Cells(i).Value
        
        ' Find the matching column in Sheet1
        On Error Resume Next
        Set sourceCol = wsSource.Rows(1).Find(What:=colName, LookIn:=xlValues, LookAt:=xlWhole)
        On Error GoTo 0
        
        If Not sourceCol Is Nothing Then
            ' Get the last row with data in the source column
            lastRow = wsSource.Cells(wsSource.Rows.Count, sourceCol.Column).End(xlUp).Row
            
            ' Skip if column only has a header (no data)
            If lastRow > 1 Then
                ' Generate a random row number (skipping the header row)
                randomRow = Int((lastRow - 1) * Rnd + 2)
                
                ' Copy the random cell to the target range
                wsSource.Cells(randomRow, sourceCol.Column).Copy targetPasteRange.Cells(i)
                
                ' Add the value to our concatenated text
                concatText = concatText & wsSource.Cells(randomRow, sourceCol.Column).Value & " "
            Else
                ' Handle empty column scenario
                targetPasteRange.Cells(i).Value = "No data in column"
                concatText = concatText & "No data in column" & " "
            End If
        Else
            ' Handle invalid column name selection
            targetPasteRange.Cells(i).Value = "Column not found"
            concatText = concatText & "Column not found" & " "
        End If
    Next i
    
    ' Trim extra space and paste the final concatenated text
    concatCell.Value = Trim(concatText)
End Sub

• Range Tweaks: Make sure you update the range variables (dropDownRange, targetPasteRange, concatCell) to match your actual Sheet2 layout.
• Randomness: If you want new random results every time you open the workbook, add Randomize at the very start of the sub (right after Sub RandomSelectAndConcatenate()).
• Dropdown Setup: If you haven’t set up the dropdowns yet, use Data Validation: select your dropdown cells > Data tab > Data Validation > List > Source = your Sheet1 column headers (e.g., Sheet1!$A$1:$D$1).
• Error Handling: The code includes basic checks for empty columns or invalid dropdown picks, so it won’t crash unexpectedly.

1. Right-click the command button on Sheet2
2. Select "Assign Macro"
3. Pick RandomSelectAndConcatenate from the list and click OK

That’s all! Now when you select options from the dropdowns and click the button, it’ll pull random cells from Sheet1, paste them to your target area, and stack all the selected values into one cell as requested.

内容的提问来源于stack exchange，提问作者Natalie Hunt