Run-time error '1004'

  • Thread starter Thread starter JSnow
  • Start date Start date
J

JSnow

I'm getting an error when sheet2 (or "Half Payout") is opened IF there is
only 1 row of data present. I have a header in row 2. If the sheet has
nothing in row 3 or if there is more than one row it works fine. Here is the
exact error followed by the exact coding:

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

Private Sub Worksheet_Activate()

Dim gotorow As Long

Sheet2.Range("B3:I99").sort key1:=Range("D3"), order1:=xlAscending,
key2:=Range( _
"E3"), order2:=xlAscending, key3:=Range("B3"), order3:=xlAscending, _
Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, dataoption1:=xlSortNormal, dataoption2:=xlSortNormal, _
dataoption3:=xlSortNormal

If Sheet2.Range("B3") = "" Then
gotorow = 3
Else
gotorow = Sheet2.Range("B3").End(xlDown).Row + 1
End If
Sheet2.Range("B" & gotorow).Select ' LINE WITH ERROR

End Sub

When I step into the error and hover over the gotorow variable it displays
"gotorow = 65537".
 
Please try the below code. Please refer Sheet2 always before the Range() and
activate Sheet2 before you select the range.

Dim gotorow As Long

Sheet2.Range("B3:I99").Sort key1:=Sheet2.Range("D3"),
order1:=xlAscending, key2:=Sheet2.Range( _
"E3"), order2:=xlAscending, key3:=Sheet2.Range("B3"),
order3:=xlAscending, _
Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, dataoption1:=xlSortNormal, dataoption2:=xlSortNormal, _
dataoption3:=xlSortNormal

If Sheet2.Range("B3") = "" Then
gotorow = 3
Else
gotorow = Sheet2.Range("B3").End(xlDown).Row + 1
End If
Sheet2.Activate
Sheet2.Range("B" & gotorow).Select ' LINE WITH ERROR


If this post helps click Yes
 
Unfotunately, I'm getting the exact same error, same line, and the variable
"gotorow" still equals 65536
 
Back
Top