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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
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)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
All your constants should be declared as Long, not as String.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,079
Members
414,426
Latest member
fraru

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
Top