Object required??? What object?

[jlclyde]

Here is the code that I am using and it is stored in the worksheet
itself. Is this the problem? Why else woudl I be getting Object
Required error?

Thanks,
Jay

Sub HeidDay()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i, c
Dim Rng As Range
Dim Rnge As Range
Dim Target As Range

Range("A5:HW1000").Sort Key1:=Range("A5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Set Target = Sheet19.Range("A5")
Do Until Target.Value = ""
If Target.Value <= Date - 90 And DatePart("ww", Target.Value)
= _
DatePart("ww", Target.Offset(1, 0).Value) Then

Set Rng = Sheet19.Range(Cells(Target.Row, 2), _
Cells(Target.Row, 41))
Set Rnge = Sheet19.Range(Cells(Target.Row, 207), _
Cells(Target.Row, 220))

For Each i In Rng
If IsNumeric(i.Value) And
IsNumeric(i.Offset(1, 0).Value) Then
i.Value = i.Value + i.Offset(1, 0).Value
End If
Next i

For Each c In Rnge
If IsNumeric(c.Value) And
IsNumeric(c.Offset(1, 0).Value) Then
c.Value = c.Value + c.Offset(1, 0).Value
End If
Next c

Sheet19.Range(Cells(i.Row + 1, 1), Cells(i.Row + 1,
41)).Delete _
Shift:=xlUp
Sheet19.Range(Cells(c.Row + 1, 207), Cells(c.Row + 1,
220)). _
Delete Shift:=xlUp
Else
GoTo P
End If
P:
Set Target = Target.Offset(1, 0)
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Gary''s Student]

Set Target = Sheet19.Range("A5")

So where is Sheet19 defined or initialized??

 

[JE McGimpsey]

Since you didn't say where the error occurs, it's impossible to tell for
sure.

One possibility:

If the code isn't in Sheet19, then

Set Rng = Sheet19.Range(Cells(Target.Row, 2), Cells(Target.Row, 41))

is problematic, because an unqualified Cells() call defaults to the
active sheet, i.e.:

Set Rng = Sheet19.Range(Me.Cells(Target.Row, 2), _
Me.Cells(Target.Row, 41))

which will fail since a range can't include multiple sheets.

Better:

With Sheet19
Set Rng = .Range(.Cells(Target.Row, 2), _
.Cells(Target.Row, 41))
End With

 

[Joel]

There wre about 100 Object errors for many different reasons.

1) when you have a range like Range(cells(1,1),cells(10,20)) you ned to
have a sheet refference for the Range and the cells. It is better to do
something like this

with Sheets("Sheet1")
.Range(.cells(1,1),.cells(10,20)) '< Added three periods
end with

2) for loop counters are not valid outside the loop.

3) Sheet19 is the code sheet and not the tab sheet name it is better to
use the tab name like this

Sheets("Sheet19"


)
Sub HeidDay()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i, c
Dim Rng As Range
Dim Rnge As Range
Dim Target As Range

Range("A5:HW1000").Sort _
Key1:=Range("A5"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With Sheets("Sheet19")
Set Target = .Range("A5")
Do Until Target.Value = ""
If Target.Value <= Date - 90 And DatePart("ww", Target.Value) = _
DatePart("ww", Target.Offset(1, 0).Value) Then

Set Rng1 = .Range(.Cells(Target.Row, 2), _
.Cells(Target.Row, 41))
Set Rng2 = .Range(.Cells(Target.Row, 207), _
.Cells(Target.Row, 220))

For Each i In Rng1
If IsNumeric(i.Value) And _
IsNumeric(i.Offset(1, 0).Value) Then

i.Value = i.Value + i.Offset(1, 0).Value
End If
Next i

For Each c In Rng2
If IsNumeric(c.Value) And _
IsNumeric(c.Offset(1, 0).Value) Then

c.Value = c.Value + c.Offset(1, 0).Value
End If
Next c

Rng1.Delete _
Shift:=xlUp
Rng2.Delete _
Shift:=xlUp
Set Target = Target.Offset(1, 0)
End If
Loop
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[JE McGimpsey]

I would have assumed that it's the code name of a worksheet

 

[JE McGimpsey]

it is better to use the tab name

Usually not - unless you disable the user's ability to change the sheet
name.

No reason to let the user break your code so easily.

 

[Joel]

It is easier to debug a worksheet when you use Tab names. The tcode names
are not visible from the workbook. You also get situations where the code
name sheet19 and the tab name sheet18.

 

[Dave Peterson]

Then it would be a very good idea to change the code name to something more
meaningful.

Instead of Sheet19, you could rename the code name to Prices (if it contained
prices).

Then
prices.range("a1").value = "new value here"
would make sense to the developer.

 

[JE McGimpsey]

It is easier to debug a worksheet when you use Tab names. The tcode names
are not visible from the workbook. You also get situations where the code
name sheet19 and the tab name sheet18.

When debugging, I'm generally in the VBE, so I can see the code names. I
also tend to set the code names to be meaningful. Your mileage obviously
varies.

However, even if it's easier for you to debug with worksheet names, you
probably should convert your final code to use code names to prevent a
user from inadvertently breaking your code.