VBA实现Excel两列数据随机拼接时Range方法调用报错如何解决

• 语法误用：Cells(行号, 列号) 是VBA原生的单元格引用方法，不需要额外嵌套Range()函数
• 变量解析失败：你将Cells(randomNum1, 1)写在了双引号内部，VBA会把这段内容识别为普通的单元格地址文本，而非带变量的代码，系统找不到名为"Cells(randomNum1, 1)"的单元格，就会触发Range方法调用失败的报错。

Sub Createrandomwords()

Dim randomNum1 As Integer
Dim randomNum2 As Integer
Dim i As Integer

'C列会生成8行随机拼接结果
For i = 1 To 8
    randomNum1 = WorksheetFunction.RandBetween(1, 26) 'A列总共有26行数据
    randomNum2 = WorksheetFunction.RandBetween(1, 33) 'B列总共有33行数据
    Cells(i, 3) = Cells(randomNum1, 1) & " " & Cells(randomNum2, 2)
Next i

End Sub

如果你后续A、B列的数据行数会变动，不想每次手动修改随机数的上限，可以用动态获取行号的写法，避免硬编码出错：

Sub Createrandomwords()

Dim randomNum1 As Integer
Dim randomNum2 As Integer
Dim i As Integer
Dim aLastRow As Integer, bLastRow As Integer

'动态获取A、B列最后一行有数据的行号
aLastRow = Cells(Rows.Count, 1).End(xlUp).Row
bLastRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To 8
    randomNum1 = WorksheetFunction.RandBetween(1, aLastRow)
    randomNum2 = WorksheetFunction.RandBetween(1, bLastRow)
    Cells(i, 3) = Cells(randomNum1, 1) & " " & Cells(randomNum2, 2)
Next i

End Sub

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