I've written the following:

Public Sub TribalInvCheck()
Dim wbTribalHist As Workbook
Dim wbTribalTR As Workbook
Dim wsTribalTR As Worksheet
Dim wsTribalHist As Worksheet
Dim rTRCell As Range
Dim lTRRow As Long
Dim lHistRow As Long
Dim rFoundID As Range
Dim sTRID As String
Dim rTribalHist As Range
Dim lHistCol As Long
Dim rHistStart As Range
Dim rTotals As Range
Dim rDateRange As Range

Set wbTribalHist = ThisWorkbook
Set wbTribalTR = ActiveWorkbook
Set wsTribalTR = ActiveSheet
Set wsTribalHist = wbTribalHist.Worksheets("Historical")

Set rTribalHist = wsTribalHist.Range("A3:IV150")

'Application.ScreenUpdating = False

If ThisWorkbook.Name = ActiveWorkbook.Name Then
MsgBox "Please do not run this macro from the workbook that contains it."
_
& Chr(10) & "Please select a Turnaround Report and then restart this
macro."
Exit Sub
End If


'rTRCell.Select

'### Add code to add new client to Tribal Invoice Check if client not found


'wsTribalHist.Activate
'rFoundID.Select

lTRRow = 3
wsTribalHist.Activate

Cycle:
'Do loop until totals column shows "Monthly Totals"
Do

Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
' wsTribalTR.Activate
' rTotals.Select

If rTotals.Value = "Totals" Then
lTRRow = lTRRow + 1
GoTo Cycle
End If
Set rDateRange = Cells(rTotals.Offset(0, -1), rTotals) <---Application
defined or object defined error.
rDateRange.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
Loop Until rTotals = "Monthly Totals"

End Sub

I'm thinking it's a syntax error where I should have put a "(" instead of a
"." or vice versa. Something like that. Any suggestions?
Thanks again.

Change cells to range... Cells expect 2 arguments corresponding to row and
column coordinates. You have passed it 2 range objects.
--
HTH...

Jim Thomlinson


"salgud" wrote:

I've written the following:

Public Sub TribalInvCheck()
Dim wbTribalHist As Workbook
Dim wbTribalTR As Workbook
Dim wsTribalTR As Worksheet
Dim wsTribalHist As Worksheet
Dim rTRCell As Range
Dim lTRRow As Long
Dim lHistRow As Long
Dim rFoundID As Range
Dim sTRID As String
Dim rTribalHist As Range
Dim lHistCol As Long
Dim rHistStart As Range
Dim rTotals As Range
Dim rDateRange As Range

Set wbTribalHist = ThisWorkbook
Set wbTribalTR = ActiveWorkbook
Set wsTribalTR = ActiveSheet
Set wsTribalHist = wbTribalHist.Worksheets("Historical")

Set rTribalHist = wsTribalHist.Range("A3:IV150")

'Application.ScreenUpdating = False

If ThisWorkbook.Name = ActiveWorkbook.Name Then
MsgBox "Please do not run this macro from the workbook that contains it."
_
& Chr(10) & "Please select a Turnaround Report and then restart this
macro."
Exit Sub
End If


'rTRCell.Select

'### Add code to add new client to Tribal Invoice Check if client not found


'wsTribalHist.Activate
'rFoundID.Select

lTRRow = 3
wsTribalHist.Activate

Cycle:
'Do loop until totals column shows "Monthly Totals"
Do

Set rTRCell = wsTribalTR.Cells(lTRRow, "A")
sTRID = rTRCell.Value
Set rFoundID = rTribalHist.Find(sTRID, LookIn:=xlValues)
lHistRow = rFoundID.Row + 2
lHistCol = rFoundID.Column
Set rHistStart = wsTribalHist.Cells(lHistRow, lHistCol)
Set rTotals = rTRCell.Offset(0, 7)
' wsTribalTR.Activate
' rTotals.Select

If rTotals.Value = "Totals" Then
lTRRow = lTRRow + 1
GoTo Cycle
End If
Set rDateRange = Cells(rTotals.Offset(0, -1), rTotals) <---Application
defined or object defined error.
rDateRange.Copy Destination:=rHistStart
lHistRow = lHistRow + 1
Loop Until rTotals = "Monthly Totals"

End Sub

I'm thinking it's a syntax error where I should have put a "(" instead of a
"." or vice versa. Something like that. Any suggestions?
Thanks again.

On Fri, 25 Jul 2008 15:08:10 -0700, Jim Thomlinson wrote:

Change cells to range... Cells expect 2 arguments corresponding to row and
column coordinates. You have passed it 2 range objects.

Thanks, Jim! Maybe I'll figure out how cells and ranges work in another 5
yrs or so.