I'm writing a small class in Excel VBA with a sub that takes Range as an argument. But when I try to call it using Cells I get type mismatch error. (Calling using Range works fine)
Code excerpt:
Dim Com, Gua As Worksheet
Set Com = Worksheets(cCom)
Set Gua = Worksheets(cGua)
Dim myFirm As clsFirm
Set myFirm = New clsFirm
Call myFirm.Init(Gua.Cells(3, 4)) '<--ERROR HERE
Call myFirm.Init(Gua.Range("E3")) ' this line works
And here is the sub:
Public Sub Init(nameCell As Range)
pAddress = nameCell.Address
pName = nameCell.Value2
pContract = nameCell.Offset(0, -3).Value2
pAmount = nameCell.Offset(0, 2).Value2
pRate = nameCell.Offset(0, 1).Value2
pStartDate = CDate(nameCell.Offset(0, -2).Value2)
pEndDate = CDate(nameCell.Offset(0, -1).Value2)
End Sub