VBA代码运行正常但结果显示错误，需指定输出至Sheet2求助

Hey there! The problem with your current code is that it’s tied entirely to Sheet1—you’ve set ws to reference Sheet1, so when you write the value using .Cells(dispRow, dispCol).Value, it lands right there. To send the random word to Sheet2, we just need to separate your data source (Sheet1) from your target output sheet (Sheet2). Here’s the adjusted code, plus breakdowns of the changes:

Modified Working Code #

Sub showRandomWord()
    Dim sourceWs As Worksheet ' For your data in Sheet1
    Dim targetWs As Worksheet ' For output to Sheet2
    Dim stRow As Long, endRow As Long, dataCol As Long
    Dim dispRow As Long, dispCol As Long
    
    ' Assign your data source and target sheets
    Set sourceWs = Sheets("Sheet1")
    Set targetWs = Sheets("Sheet2")
    
    stRow = 2 ' Start row of your data in Sheet1
    dataCol = 1 ' Column with your word list in Sheet1
    dispRow = 2 ' Row to output in Sheet2
    dispCol = 4 ' Column to output in Sheet2
    
    With sourceWs
        ' Find the last row of data in Sheet1's target column
        endRow = .Cells(.Rows.Count, dataCol).End(xlUp).Row
        ' Pull a random value from Sheet1 and write it to Sheet2
        targetWs.Cells(dispRow, dispCol).Value = .Cells(Application.WorksheetFunction.RandBetween(stRow, endRow), dataCol).Value
    End With
End Sub

Key Changes Explained #

• Separate Sheet References: I added sourceWs for Sheet1 (where your word list lives) and targetWs for Sheet2 (where you want the result). This makes the code easier to read and modify later.
• Redirect Output: Instead of writing to ws.Cells(...), we now use targetWs.Cells(dispRow, dispCol).Value to send the random value straight to Sheet2’s specified cell.
• Completed the RandBetween Call: Your original code cut off at Applicat...—I filled in the full Application.WorksheetFunction.RandBetween(stRow, endRow) to make the random selection work properly.

If you ever need to adjust either the data source or output sheet later, you just change the Set ... = Sheets("...") lines—no hunting through the rest of the code!

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