Fill down problem with Worksheet_Change(ByVal Target As Range)

[Guest]

Hi

I have a problem when trying to use "fill down" in an excel sheet where I am
using a Worksheet_Change() program to update some cells. The program looks
like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoDoc

If Target.Row > 4 Then
Select Case Target.Column
Case 29
NoDoc = "No Doc."
=> If Target.Offset(0, 0) <> NoDoc And Target.Offset(0, -4) = "" Then
Target.Offset(0, -4) = "XXXXXX"
ElseIf Target.Offset(0, 0) = NoDoc Then
Target.Offset(0, -4) = ""
End If
End Select
End If
End Sub

The errormessage is "Runtime error 13 - Type Mitchmatch". The Sub is working
okay when only change one cell at a time - the error arrices when cliking a
cell and draging it down.

Can anyone help?

/Steen

 

[paul.robinson]

Hi
If you select more than one cell, your conditions on Target do not make
sense. You need to check the conditions in each Cell of target.
Target.row still makes sense, and returns the first row in the range
Target.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoDoc As String
Dim Cell as range
If Target.Row > 4 Then
Select Case Target.Column
Case 29
NoDoc = "No Doc."
For Each cell In Target
If cell <> NoDoc And cell.Offset(0, -4) = "" Then
cell.Offset(0, -4) = "XXXXXX"
ElseIf cell = NoDoc Then
cell.Offset(0, -4) = ""
End If
Next cell
End Select
End If
End Sub

regards
Paul

 

[Guest]

Hi Poul

Thanks - works wery well

/Steen

Hi
If you select more than one cell, your conditions on Target do not make
sense. You need to check the conditions in each Cell of target.
Target.row still makes sense, and returns the first row in the range
Target.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoDoc As String
Dim Cell as range
If Target.Row > 4 Then
Select Case Target.Column
Case 29
NoDoc = "No Doc."
For Each cell In Target
If cell <> NoDoc And cell.Offset(0, -4) = "" Then
cell.Offset(0, -4) = "XXXXXX"
ElseIf cell = NoDoc Then
cell.Offset(0, -4) = ""
End If
Next cell
End Select
End If
End Sub

regards
Paul