在 VBA 中调用 Sub 时出错

Question

我在调用 sub 时遇到问题。 错误消息是参数数错误或属性分配无效。我尝试了许多变化，但没有任何效果。

Sub last_non_empty_cell_in_a_row()
Dim rngCell As Range, i As Long
Set rngCell = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, 
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False) '.Activate
i = rngCell.Row
End Sub

Sub code_main()
Dim x As Long
Call last_non_empty_cell_in_a_row(i)
For x = 1 To i
If Range("R" & x) = "m_M" Then
If Range("P" & x) = "m_DH" Then
If Range("Q" & x) = "" Then
Else
Range("P" & x, "R" & x).Interior.ColorIndex = 22
End If
Else
Range("P" & x, "R" & x).Interior.ColorIndex = 22
End If
Else
Range("P" & x, "R" & x).Interior.ColorIndex = 0
End If
Next x

End Sub

Answer 1

您想将last_non_empty_cell_in_a_row更改为Function并让它返回i的值。

Function last_non_empty_cell_in_a_row() As Long
Dim rngCell As Range, i As Long
Set rngCell = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, 
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False) '.Activate
i = rngCell.Row
last_non_empty_cell_in_a_row = i
End Function

然后，在调用过程中：

Sub code_main()
Dim x As Long
For x = 1 to last_non_empty_cell_in_a_row()
...

可能还有其他问题或错误，我没有测试。值得注意的是，last_non_empty_cell_in_a_row似乎是一个函数签名，并没有真正描述函数返回的内容。有关获取给定范围或工作表中"最后一个单元格"的方法，请参阅：

在VBA中查找上次使用的单元格时出错