Runtime error '1004'

Mr.M.Johansson

New Member
Joined
Aug 13, 2010
Messages
2
Hey everyone!
I have a error that occurs in the middle of the code:


Private Sub Worksheet_Change(ByVal Target As Range)
.
.
.
Sheets("Komponenter").Select
-> Range("b2").Select <-
Selection.End(xlDown).Select

.
.
.
End sub

The code is pretty long so i chosed not to post it all....
I looked around on the forum and seen similar problems with the most common solution to exclude the ".select" command and chose other commands instead, but still.....
The .select command occurs several times before in the code without any errors, i can take the same code and insert it in another sub and run it without any errors.

I will exchange most of the .select commands in time, when i stumble on better solutions but i want to be able to use the .select commands for a while more.

Is there any solution without replacing the .select command?

thankful for any help
/Martin Johansson
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Your Range("B2") is not fully qualified hence, because the code is in a sheet module, it refers to the Sheet in which the code appears. You have just selected another sheet and thus you have a mismatch (ie trying to select a cell on one sheet whilst another sheet is active). You'd need to post what the routine is meant to be doing for us to suggest alternatives.
 

Mr.M.Johansson

New Member
Joined
Aug 13, 2010
Messages
2
OK, I wrote all the comments on swedish so I just did a quick translation, hope I make any sense to you guys....
_____________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
ScreenUpdating = False

Application.EnableEvents = False
thisrow = Target.Row
thiscolumn = Target.Column

'----------------------------------------------------------------------
' If a certain area of the sheet is edited, this activates a sequence
'----------------------------------------------------------------------
'this just fill in text such as F1..F2...F3 and so on
If thiscolumn > 4 And thiscolumn < 9 And thisrow > 2 And Not Cells(thisrow, thiscolumn).Interior.ColorIndex = 6 Then
Cells(3, 4) = "F1"
Cells(4, 4) = "F2"

If thisrow > 4 Then
Range(Cells(3, 4), Cells(4, 4)).Select
Selection.AutoFill Destination:=Range(Cells(3, 4), Cells(thisrow, 4)), Type:=xlFillDefault
Range("D3:D4").Select
End If

'----------------------------------------------------------------------
' Filtering a "database located on another sheet according to the
' information entered
'----------------------------------------------------------------------
I1 = Cells(thisrow, 5)
I2 = Cells(thisrow, 6)
I3 = Cells(thisrow, 7)
I4 = Cells(thisrow, 8)
Sheets("Komponenter").Select
If Not I1 = "" Then Selection.AutoFilter Field:=1, Criteria1:=I1 Else Selection.AutoFilter Field:=1
If Not I2 = "" Then Selection.AutoFilter Field:=2, Criteria1:=I2 Else Selection.AutoFilter Field:=2
If Not I3 = "" Then Selection.AutoFilter Field:=3, Criteria1:=I3 Else Selection.AutoFilter Field:=3
If Not I4 = "" Then Selection.AutoFilter Field:=4, Criteria1:=I4 Else Selection.AutoFilter Field:=4
Sheets("Startsida").Select
Range("q35").Select


'----------------------------------------------------------------------
' Copys the data from the database when there is 10 rows or less 'remaining from the filtering
'----------------------------------------------------------------------
Sheets("Komponenter").Select
Range("b2").Select
Selection.End(xlDown).Select

crow = ActiveCell.Row
ccolumn = ActiveCell.Column

If crow < 10 Then
Range(Cells(crow, ccolumn - 1), Cells(2, 5)).Select
Selection.Copy


Sheets("Startsida").Select
Range("d9").Select
Selection.Insert Shift:=xlDown

Range(Cells(thisrow - 1, 4)).Select
ActiveCell.Offset(-crow - 1, ccolumn).Select
Call RamaInMarkering '<- Just changes the color of the selected cells

End If

'-----------------------------------------------------------------------------------------------

'-----------------------------------------------------------------------------------------------

Application.EnableEvents = True
Sceenuppdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top