Error 13, Type mismatch help please

[RobertBon]

Hello,

I'm developing a code that is suppose to convert the NumberFormatt of a number depending on it's unit (m3 or L)

My code works for the most part but once I try to delete more than one cell at a time, the program crashes and a debugger window opens saying "run time error 13: Type Mismatch". Below I posted my code can anyone help remove this message or is there something wrong with my code?

'converts decmial places depending on L/m3
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngIntersect As Range

Set rngIntersect = Intersect(Range("T:T"), Target)

If Not rngIntersect Is Nothing Then
If Target.Value = "L" Then DEBUGGER POINTS TO THIS LINE
Target.Offset(0, -1).NumberFormat = "0"
Else
Target.Offset(0, -1).NumberFormat = "0.000"
End If
End If

End Sub

 

[JoeMo]

Your code works fine for me.

 

[RobertBon]

My chart in excel looks something like this ('Unit' is in the "T" column)
<table border="0" cellpadding="0" cellspacing="0" width="196"><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> <col style="mso-width-source:userset;mso-width-alt:1718;width:35pt" width="47"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:112pt" height="17" width="149">Volume</td> <td class="xl25" style="width:35pt" width="47">Units</td> </tr> <tr style="mso-height-source:userset;height:2.25pt" height="3"> <td class="xl26" style="height:2.25pt" height="3">
</td> <td class="xl27">
</td> </tr> <tr style="mso-height-source:userset;height:15.75pt" height="21"> <td class="xl31" style="height:15.75pt;height:15.75pt; mso-ignore:style;background:#FFFFCC;mso-pattern:auto none" height="21" width="149"> 56</td> <td class="xl28" style="width:35pt;mso-ignore:style;background:#FFFFCC; mso-pattern:auto none" width="47">L
</td> </tr> <tr style="mso-height-source:userset;height:2.25pt" height="3"> <td class="xl30" style="height:2.25pt" height="3">
</td> <td class="xl27">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl29" style="height:15.75pt;height:15.75pt; mso-ignore:style;background:#FFFFCC;mso-pattern:auto none" height="21" width="149">42.3
</td> <td class="xl28" style="width:35pt;mso-ignore:style;background:#FFFFCC; mso-pattern:auto none" width="47">m3
</td> </tr> <tr style="mso-height-source:userset;height:2.25pt" height="3"> <td class="xl30" style="height:2.25pt" height="3">
</td> <td class="xl27">
</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td rowspan="2" class="xl35" style="border-bottom:.5pt .5pt .5pt .5pt .5pt; mso-ignore:style;background:#FFFFCC;mso-pattern:auto none" height="36" width="149">45.1
</td> <td rowspan="2" class="xl32" style="border-bottom:.5pt .5pt .5pt .5pt; mso-ignore:style;background:#FFFFCC;mso-pattern:auto none" width="47"> m3</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> </tr> </tbody></table>

If you delete all the values at the same time there's no error message?

 

[Jon von der Heyden]

The problem is that if the range is more than one cell you can't have the statement:

Target.Value = "L"

Instead you should define a range variable and loop through each cell in Target.

For Each rngCell in Target
    If rngCell.Value = "L" Then
        rngCell.Offset(0, -1).NumberFormat = "0"
    Else
        rngCell.Offset(0, -1).NumberFormat = "0.000"
    End If
Next rngCell

 

[RobertBon]

hahahaha thanks it's working now Thanks a lot!