Run-time error 13 Type Mismatch

[dosborn]

Thank you in advance for any help. I am getting a run-time error 13 type mismatch and can not figure it out. Below are the two macros I am running:

Private Sub Worksheet_Change(ByVal Target As range)
If Intersect(Target, range("D11:D34")) Is Nothing Then
Exit Sub
Else
Call PartBalance
End If
End Sub
_______________________________________________________

Sub PartBalance()

With Sheets("REQUEST")

If range("D11:D34").Value - range("K11:K34").Value <> 0 Then

range("B11:B34").Copy Sheets("REQUISITION").range("M11:M22,M31:M42")
range("E11:E34").Copy Sheets("REQUISITION").range("E11:E22,E31:E42")
range("H11:H34").Copy Sheets("REQUISITION").range("H11:H22,H31:H42")

End If

End With


Thank you again for any help.

 

[Norie]

Have you declared a variable with the name 'range'?

Or called a sub/module 'range'?

 

[dosborn]

No I have not.

 

[Norie]

The reason I ask is because in the posted code range is in lower case.

It should be in proper case, Range.

However that's not the problem.

The problem is in the sub you are calling.

You can't do this.

If range("D11:D34").Value - range("K11:K34").Value <> 0 Then

You can't subtract 2 ranges like this.

What is it you actually want to subtract anyway?

 

[dosborn]

It is quantity of parts to order and I need to determine if parts were pulled from stock and partial filled so K11 through K34 is greater than zero it will prompt me to order parts.

 

[Norie]

Do you mean if the sum of K11 to K34 is more than 0 or if any of the values in the range are greater than 0?

Also, where does the other range, D11:D34, come into it?

 

[dosborn]

Sorry about that.
Column D is the number of parts requested.
Column K is the number of parts handed out.

So if (D)-(K) is greater than 0 i need the macro to prompt me.

I have it working for a single row 11, but I need to incorporate rows 12 through 34.

Thanks for the help.