Error while Call Sub in VBA

Question

I am facing problem while calling sub. Error message is wrong number of arguments or invalid property assignment. I tried many variations and nothing worked at all.

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

You are passing `i` into `last_non_empty_cell_in_a_row`. The Sub does not take a parameter. — Brian M Stafford, Jul 27 '17 at 13:28

score 2 · Accepted Answer · answered Jul 27 '17 at 13:31

You want to change last_non_empty_cell_in_a_row to a Function and have it return the value of 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

And then, in the calling procedure:

Sub code_main()

Dim x As Long

For x = 1 to last_non_empty_cell_in_a_row()
...

There may be other problems or errors, I did not test. Notably, last_non_empty_cell_in_a_row seems to be a function signature that does not really describe what the function returns. For ways to get the "last cell" in a given range or sheet, see:

Error in finding last used cell in VBA

Another option is to modify the Sub to accept a ByRef parameter. — Brian M Stafford, Jul 27 '17 at 13:35
Thanks! That was very useful and helpful. It is working fine. Anyway, converting it from Sub() into function() makes unable to call that macro separately, right? — RafMil, Jul 27 '17 at 14:23
@RafMil yes, you won't be able to call it like from the Macro menu. — David Zemens, Jul 27 '17 at 14:35

Error while Call Sub in VBA

1 Answers1