Runtime error '1004'

[Mr.M.Johansson]

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

 

[Richard Schollar]

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]

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