Type mismatch error'13'

[Guest]

anyone see why this creats this error
Thanking you in advance

If target.Column = 10 And target.Value > 10 Then

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Select Case sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value > 10 Then
Call CopyStuff(sh, target)
End If
End Select
End Sub

 

[Dave Peterson]

If there's an error in the changed range, then target.value will cause an error.

If you change more than one cell, then target.value can't be compared to a
single number.

 

[Guest]

The code you have posted is fine. This works for me... (I don't have CopyStuff)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Select Case sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value > 10 Then
MsgBox sh.Name & " " & target.Value
End If
End Select
End Sub

I suspect that you have declared CopyStuff incorrectly. It should be
something like

Public Sub CopyStuff (byval sh as Worksheet, byval target as Range)

 

[merjet]

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

 

[Dave Peterson]

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

 

[Guest]

Will enclose copystuff code

The code you have posted is fine. This works for me... (I don't have CopyStuff)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Select Case sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value > 10 Then
MsgBox sh.Name & " " & target.Value
End If
End Select
End Sub

I suspect that you have declared CopyStuff incorrectly. It should be
something like

Public Sub CopyStuff (byval sh as Worksheet, byval target as Range)

Public Sub CopyStuff(ByVal Sh As Worksheet, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rngPaste = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub

 

[Guest]

here is complete code of this novice Want entry in any cell in column 'J'
over $10.00 to trigger this code.Column 'J' is only column set to currency.
Donors sheet that data will transfer to has first row frozen for labels &
scrolling. Do we need to insert a row to paste ?
Thanks much for your help

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste As Range
Select Case Sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value > 10 Then
Call CopyStuff(Sh, target)
End If
End Select
Public Sub CopyStuff(ByVal Sh As Worksheet, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rngPaste = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub

 

[Guest]

Try something more like...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste As Range
If target.Cells.Count <> 1 Then Exit Sub
Select Case Sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value > 10 Then
Call CopyStuff(target)
End If
End Select

End Sub

Public Sub CopyStuff(ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range

Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

rngPaste.Value = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub

 

[Guest]

Thanks much Jim will see what I can do.
Thanks Again

 

[Guest]

end if befor end select
error end if without block if
' use comment on end if an no responce from code?
WOW
copied code comp[letely as posted