求助：如何用VBA将Sheet2内容加工后插入Sheet3指定单元格

Hey there! Since you're new to VBA, let's walk through this step by step to get your task done smoothly. 😊

What we're going to accomplish: #

• Pull fruit names and types from Sheet2 range D2:E6
• Format them into a clean, customizable text string
• Insert that formatted text into Sheet3 cell C3

Step 1: Add the VBA Code #

First, open the VBA editor (press Alt + F11 in Excel). Right-click your workbook in the Project Explorer > Insert > Module, then paste this code:

Sub ProcessFruitData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim rowNum As Integer
    Dim finalText As String
    
    ' Link to your source (Sheet2) and target (Sheet3) worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet2")
    Set wsTarget = ThisWorkbook.Sheets("Sheet3")
    
    ' Start with an empty text string
    finalText = ""
    
    ' Loop through each row from 2 to 6 (covers D2:E6)
    For rowNum = 2 To 6
        ' Customize this line to match your desired text format!
        ' Example format: "- Apple: Fresh Fruit"
        finalText = finalText & "- " & wsSource.Cells(rowNum, "D").Value & ": " & wsSource.Cells(rowNum, "E").Value & vbCrLf
    Next rowNum
    
    ' Paste the formatted text into Sheet3's C3 cell
    wsTarget.Range("C3").Value = finalText
End Sub

Step 2: Tweak the Text Format (If Needed) #

The code above uses a bullet-point style, but you can adjust it to match your target effect:

• For comma-separated values: finalText = finalText & wsSource.Cells(rowNum, "D").Value & " (" & wsSource.Cells(rowNum, "E").Value & "), "
• For line-separated without bullets: finalText = finalText & wsSource.Cells(rowNum, "D").Value & " - " & wsSource.Cells(rowNum, "E").Value & vbCrLf

Step 3: Trigger the Code After Saving Form Data #

Since you're using a user form to collect data, add a call to this subroutine in your form's "Submit" button click event. Here's an example:

Private Sub cmdSubmit_Click()
    ' First, add your existing code here to save form data to Sheet2
    ' ... (your save logic goes here)
    
    ' Run our data processing subroutine
    ProcessFruitData
    
    ' Close the user form
    Me.Hide
End Sub

Quick Tips: #

• Double-check that your worksheets are named exactly Sheet2 and Sheet3 (update the code if you've renamed them)
• Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to keep the code functional
• Test with sample data first to make sure the format matches your desired output!

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