VBA error! Can't seemt to debug :(

[hmchang82]

Const CtyCount As String = 2
Const CtyName As String = 10
Const CapIQID As String = 12
Const BusUnit As String = 13
Const GovnDoc As String = 50
Const LegStat As String = 51
Const LegStatDate As String = 52
Const LegDocOwner As String = 53
Const LegContactName As String = 54
Const LegContactNum As String = 55
Const LegComments As String = 56
Sub Legal()
Dim wbData, wbLegData As Workbook
Dim wsData, wsLegData As Worksheet
Dim r, c As Long
Set wbData = ThisWorkbook
Set wsData = wbData.Sheets("MasterCredit and Legal Database")
Set wbLegData = Application.Workbooks.Open("S:\Credit\Grace\Laptop\From\Test.xlsx")
Set wsLegData = wbLegData.Sheets("Sheet1")

For r = 19 To ActiveSheet.UsedRange.Rows.Count

If Trim(wsData.Cells(r, CtyName).Value) = Trim(wsLegData.Cells(r, 10).Value) _
And Trim(wsData.Cells(r, BusUnit).Value) = Trim(wsLegData.Cells(r, 12).Value) Then
If Trim(wsData.Cells(r, LegComments).Value) = "" Then

Trim(wsData.Cells(r, 50).Value) = Trim(wsLegData.Cells(r, 13).Value)
Trim(wsData.Cells(r, 51).Value) = Trim(wsLegData.Cells(r, 14).Value)
wsData.Cells(r, LegStatDate).Value = wsLegData.Cells(r, 15).Value
Trim(wsData.Cells(r, LegDocOwner).Value) = Trim(wsLegData.Cells(r, 16).Value)
Trim(wsData.Cells(r, LegContactName).Value) = Trim(wsLegData.Cells(r, 17).Value)
wsData.Cells(r, LegContactNum).Value = wsLegData.Cells(r, 18).Value
Trim(wsData.Cells(r, LegComments).Value) = Trim(wsLegData.Cells(r, 19).Value)

ElseIf Trim(wsData.Cells(r, LegComments).Value) <> "" And wsData.Cells(r, LegStatDate).Value < wsLegData.Cells(r, 15).Value Then
Trim(wsData.Cells(r, GovnDoc).Value) = Trim(wsLegData.Cells(r, 13).Value)
Trim(wsData.Cells(r, LegStat).Value) = Trim(wsLegData.Cells(r, 14).Value)
wsData.Cells(r, LegStatDate).Value = wsLegData.Cells(r, 15).Value
Trim(wsData.Cells(r, LegComments).Value) = Trim(wsLegData.Cells(r, 19).Value)

End If
End If
Next


On the red above, I keep on getting "Application-defined or object-defined error and can't seem to find why... Any suggestions?

 

[Derek Brown]

Could the error actually be on the next line?

Trim(wsData.Cells(r, 50).Value) = Trim(wsLegData.Cells(r, 13).Value)
' should perhaps be:
wsData.Cells(r, 50).Value = Trim(wsLegData.Cells(r, 13).Value)

 

[Richard Schollar]

You appear to have two Thens on that line which would give you an error.

 

[RoryA]

All your constants should be declared as Long, not as String.