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?
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?