| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
| Run-Time error '1004' : Select method of Range class failed | Excel Discussion (Misc queries) | |||
| Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming | |||
| Run-time 1004 error on range select | Excel Programming | |||
Linear Mode
