Run-time error '1004' on Range.Activate
 

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

Run-time error '1004' on Range.Activate
 

You can only active a cell within the selected range. Therefore,if A1 is
seleected and you are activating E1, an error occurs. You could however
select A1:G10 and the activate E1 without any problem bacause E1 is within
A1:G10.

So you would have to do:
Range("E2").Select 'insert this line
Range("E2").Activate
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Don Rouse" wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

Run-time error '1004' on Range.Activate
 

I can't replicate your error but you could rewrite this code to move the rows
from one sheet to the next without switching between the sheets eg

Sheets("Weekly List").Range("A4").EntireRow.Copy _
Destination:=Sheets("Bid List").Range("A10")
Sheets("Weekly List").Range("A4").EntireRow.Delete

In order to put this into a loop for you I would need to understand what you
are trying to achieve. The code as it stands now moves every second row that
has a numeric value 0 in column K until it hits 10 consecutive rows where
column K is blank.

Regards
Rowan

"Don Rouse" wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

Run-time error '1004' on Range.Activate
 

Hi Sebastien

I have to disagree with you on this.

If you have a range selected, Say A1:C10 then you can use the activate
statement to move the activecell within the selection. So:

With ActiveSheet
.Range("A1:C10").Select
.Range("B1").Activate
End With

leaves you with Range A1:C10 selected and B1 is the activecell.

However if you use the activate statement on a cell which occurs outside the
selected range then you simply Select and Activate that cell. So:

With ActiveSheet
.Range("A1:C10").Select
.Range("F1").Activate
End With

leaves you with just cell F1 selected (and hence the active cell). I have
never seen this cause an error.

Regards
Rowan

"sebastienm" wrote:

You can only active a cell within the selected range. Therefore,if A1 is
seleected and you are activating E1, an error occurs. You could however
select A1:G10 and the activate E1 without any problem bacause E1 is within
A1:G10.

So you would have to do:
Range("E2").Select 'insert this line
Range("E2").Activate
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Don Rouse" wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

Run-time error '1004' on Range.Activate
 

Your code is behind a worksheet, right?

These two lines would work ok if you were in a general module, but fail behind
the worksheet:

Sheets("Bid list").Activate
Range("E2").Activate

The unqualified range ("range("e2").activate") line refers to the sheet that
contains the code. And since "Bid list" is now active, you're trying to
activate a cell on a sheet that isn't active.

You could do:

sheets("bid list").activate
sheets("bid list").range("e2").activate

Or

with sheets("bid list")
.activate
.range("e2").activate
....
end with



Don Rouse wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

--

Dave Peterson

Run-time error '1004' on Range.Activate
 

You're right Rowan, i just tried and it didn't behave as i thought: it didn't
return any error.
However, the online help about the Activate method of the Range object says:
"Activates a single cell, which Must be inside the current selection. " They
don't say what should happen if outside of the selection. In their example,
they Select before Activating too. I almost never activate/select a range so
i can't remember any stable/unstable situation.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Rowan" wrote:

I can't replicate your error but you could rewrite this code to move the rows
from one sheet to the next without switching between the sheets eg

Sheets("Weekly List").Range("A4").EntireRow.Copy _
Destination:=Sheets("Bid List").Range("A10")
Sheets("Weekly List").Range("A4").EntireRow.Delete

In order to put this into a loop for you I would need to understand what you
are trying to achieve. The code as it stands now moves every second row that
has a numeric value 0 in column K until it hits 10 consecutive rows where
column K is blank.

Regards
Rowan

Run-time error '1004' on Range.Activate
 

Hi Sebastien

I too try to avoid selecting/activating cells which is why I suggested the
[.copy Destination:=] format.

Thanks to Dave Peterson for pointing out to us both (and the OP) what was
causing the error.

Kind Regards
Rowan

"sebastienm" wrote:

You're right Rowan, i just tried and it didn't behave as i thought: it didn't
return any error.
However, the online help about the Activate method of the Range object says:
"Activates a single cell, which Must be inside the current selection. " They
don't say what should happen if outside of the selection. In their example,
they Select before Activating too. I almost never activate/select a range so
i can't remember any stable/unstable situation.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Rowan" wrote:

I can't replicate your error but you could rewrite this code to move the rows
from one sheet to the next without switching between the sheets eg

Sheets("Weekly List").Range("A4").EntireRow.Copy _
Destination:=Sheets("Bid List").Range("A10")
Sheets("Weekly List").Range("A4").EntireRow.Delete

In order to put this into a loop for you I would need to understand what you
are trying to achieve. The code as it stands now moves every second row that
has a numeric value 0 in column K until it hits 10 consecutive rows where
column K is blank.

Regards
Rowan

Run-time error '1004' on Range.Activate
 

Thank you, Dave.

The code is behind a worksheet. I didn't know it would not act on another
sheet.
--
Don


"Dave Peterson" wrote:

Your code is behind a worksheet, right?

These two lines would work ok if you were in a general module, but fail behind
the worksheet:

Sheets("Bid list").Activate
Range("E2").Activate

The unqualified range ("range("e2").activate") line refers to the sheet that
contains the code. And since "Bid list" is now active, you're trying to
activate a cell on a sheet that isn't active.

You could do:

sheets("bid list").activate
sheets("bid list").range("e2").activate

Or

with sheets("bid list")
.activate
.range("e2").activate
....
end with



Don Rouse wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

--

Dave Peterson

Run-time error '1004' on Range.Activate
 

Thank you, Rowen. I appreciate your help.

I see where my code would not pickup the the row if it was next row. I'll
fix that.

In the case of the '1004' error, Dave was more correct. The code is behind
a worksheet and therefore would not function on another sheet.

Thanks again.
--
Don


"Rowan" wrote:

I can't replicate your error but you could rewrite this code to move the rows
from one sheet to the next without switching between the sheets eg

Sheets("Weekly List").Range("A4").EntireRow.Copy _
Destination:=Sheets("Bid List").Range("A10")
Sheets("Weekly List").Range("A4").EntireRow.Delete

In order to put this into a loop for you I would need to understand what you
are trying to achieve. The code as it stands now moves every second row that
has a numeric value 0 in column K until it hits 10 consecutive rows where
column K is blank.

Regards
Rowan

"Don Rouse" wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don

Run-time error '1004' on Range.Activate
 

Thank you, Sebastien. I appreciate your help.

In this case, Dave was more correct. The code is behind a worksheet and
therefore would not function on another sheet.
--
Don


"sebastienm" wrote:

You can only active a cell within the selected range. Therefore,if A1 is
seleected and you are activating E1, an error occurs. You could however
select A1:G10 and the activate E1 without any problem bacause E1 is within
A1:G10.

So you would have to do:
Range("E2").Select 'insert this line
Range("E2").Activate
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Don Rouse" wrote:

I am getting a run-time error '1004' on a simple activate statement.

I looked at all the postings concerning '1004' and tried the suggestions
that might apply. I also tried Range.Select. But the problem persists.

Your assistance is appreciated.

The code is as follows.

Option Explicit
Dim BlankCells

Private Sub cmdMove_Click()
Sheets("Weekly List").Activate
Range("K1").Select

BlankCells = 0
Do Until BlankCells = 10
ActiveCell.Offset(1, 0).Activate
If ActiveCell 0 And IsNumeric(ActiveCell) Then
Sheets("Bid list").Activate
Range("E2").Activate 'This is where the error
occurrs
ActiveCell.End(xlDown).Offset(1, -4).Activate
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Copy
Sheets("Bid list").Activate
ActiveSheet.PasteSpecial
Sheets("Weekly List").Activate
Rows(ActiveCell.Row).Delete
BlankCells = 0
ElseIf ActiveCell = "" Then
BlankCells = BlankCells + 1
End If
Loop

Sheets("Bid list").Activate
Range("A1").Select
Sheets("Weekly List").Activate
Range("A1").Select
End Sub

--
Don