Bug: method or data member not found!

[luolovepi]

I have a statement in a sub of a module like this:
desLastRowNumber = ActiveSheet.Cells(.Rows.count, "F").End(xlUp).Row
Call ActiveSheet.Boxes(desLastRowNumber)


AND "Public Sub Boxes(newRow As Long)" is declared in another module

When I run my program, I get the error stating that: method not found!

What's wrong with my code?
Anyone can help me? Thank you!!

 

[VoG]

Shouldn't it be

desLastRowNumber = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row

No . in front of Cells.

 

[luolovepi]

Hi VoG,

Thanks! But I think that's not the problem. Part of my sub1 code in module1 is actually like this:
With desSheet
Dim desLastRow As Range
Set desLastRow = .Cells(.Rows.count, "F").End(xlUp)
desLastRowNumber = desLastRow.Row
desLastRowNumber = desLastRowNumber + 1
Call .Boxes(desLastRowNumber)
End With

the declaration of Boxes in Module2:
Public Sub Boxes(newRow As Long)

Isn't it correct?

Shouldn't it be

desLastRowNumber = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row

No . in front of Cells.

 

[luolovepi]

I'm so sorry, here is a copy of a more complete code:
sub1 code in module1 is actually like this:
With desSheet
Dim desLastRow As Range
Dim desLastRowNumber As Long
Set desLastRow = .Cells(.Rows.count, "F").End(xlUp)
desLastRowNumber = desLastRow.Row
desLastRowNumber = desLastRowNumber + 1
Call .Boxes(desLastRowNumber)
End With

the declaration of Boxes in Module2:
Public Sub Boxes(newRow As Long)

Shouldn't it be

desLastRowNumber = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row

No . in front of Cells.

 

[Sektor]

Incorrect. When you type . before Boxes, it belongs to desSheet.
Here's correct:

With desSheet
    Call Module2.Boxes(.Cells(.Rows.count, "F").End(xlUp).Row + 1)
End With

 

[VoG]

This works for me

Sub test()
Dim desLastRow As Range
Dim desLastRowNumber As Long
Dim desSheet As Worksheet
Set desSheet = ActiveSheet
With desSheet
    Set desLastRow = .Cells(Rows.Count, "F").End(xlUp)
    desLastRowNumber = desLastRow.Row
    desLastRowNumber = desLastRowNumber + 1
    Call Boxes(desLastRowNumber)
End With
End Sub

Public Sub Boxes(newRow As Long)
MsgBox newRow
End Sub

 

[luolovepi]

WOW!! You are right!
Thank you SO MUCH!!!
And one more question to ask:
In the module2 Boxes(newRow as Long) sub, I have:
Cells(newRow, 1)

Is it correct to put a Long type parameter in Cells()?

Rgds,
Lolo

Incorrect. When you type . before Boxes, it belongs to desSheet.
Here's correct:

Dim desLastRowNumber As Long
 
With desSheet
    desLastRowNumber = .Cells(.Rows.count, "F").End(xlUp).Row
    desLastRowNumber = desLastRowNumber + 1
    Call Module2.Boxes(desLastRowNumber)
End With

 

[Sektor]

Absolutely.
And you can avoid variables with this short code:

With desSheet
    Call Module2.Boxes(.Cells(.Rows.count, "F").End(xlUp).Row + 1)
End With

 

[luolovepi]

yeah, I got it!
Thank you very much!

rdgs,
lolo

This works for me

Sub test()
Dim desLastRow As Range
Dim desLastRowNumber As Long
Dim desSheet As Worksheet
Set desSheet = ActiveSheet
With desSheet
    Set desLastRow = .Cells(Rows.Count, "F").End(xlUp)
    desLastRowNumber = desLastRow.Row
    desLastRowNumber = desLastRowNumber + 1
    Call Boxes(desLastRowNumber)
End With
End Sub
 
Public Sub Boxes(newRow As Long)
MsgBox newRow
End Sub

 

[luolovepi]

got it!
Again, thanks!

Absolutely.
And you can avoid variables with this short code:

With desSheet
    Call Module2.Boxes(.Cells(.Rows.count, "F").End(xlUp).Row + 1)
End With