VBA error! Can't seemt to debug :(

hmchang82

New Member
Joined
May 26, 2010
Messages
13
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Could the error actually be on the next line?
Code:
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)
 
Upvote 0
All your constants should be declared as Long, not as String.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top