runtime error 13 type mismatch

Hi Folks
I'm using the code below and it seems to be working ok, However in the event I right click and delete a row I get a runtime error 13 type mismatch, can anyone suggest a solution.
Thanks

'
'Modified code to move record when complete dropdown is selected
'Includes zoom to drop down and confirmation dialog box
'And changes text to upper case
'
'Zoom code found here
'http://excelribbon.tips.net/T008448_Drop-Down_List_Font_Sizes.html

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 50
intZoomDV = 100
  Application.EnableEvents = False
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo errHandler
    If rngDV Is Nothing Then GoTo errHandler
    If Intersect(Target, rngDV) Is Nothing Then
      With ActiveWindow
        If .Zoom <> intZoom Then
          .Zoom = intZoom
        End If
      End With
    Else
      With ActiveWindow
        If .Zoom <> intZoomDV Then
          .Zoom = intZoomDV
        End If
      End With
    End If
exitHandler:
  Application.EnableEvents = True
  Exit Sub
errHandler:
  GoTo exitHandler
End Sub
'
' This macro moves a row to a sheet called Complete, when it
' detects the text "OFF_HIRE" in the range "M2:M"
' http://www.excelforum.com/l/766727-asdf.html
'


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    If Flag = True Then Exit Sub
    If Not Intersect(Target, Range("m2:m" & LR)) Is Nothing Then
        If Target.Value = "OFF_HIRE" Then
        
        Dim nResult As Long
nResult = MsgBox( _
Prompt:="Would you like to Off Hire this Record?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If
            LR = Sheets("OFF_HIRE").Range("A" & Rows.Count).End(xlUp).Row + 1
            Target.EntireRow.Copy
            Sheets("OFF_HIRE").Range("A" & LR).PasteSpecial
            Flag = True
            Target.EntireRow.Delete
        End If
    End If
    Application.CutCopyMode = False
    Flag = False
'
'the code below changes text to upper case
'http://www.mrexcel.com/forum/excel-questions/66629-visual-basic-applications-convert-text-uppercase.html 
'
 Dim cell As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell = UCase(cell)
    Next
    Application.EnableEvents = True
End Sub

Hi,

Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

Any chance you can upload the workbook, anonymised if necessary so that we can see the problem in context?

Can you clarify what actions you take which results in the runtime error.

I'm assuming it's the Trailer_costs sheet that's active when this happens. I find that right clicking a row and selecting delete puts Excel into what seems a loop of some sort with much screen flashing. I needed to use task manager to close Excel.

...and what actions do you take? Please give clear details since as I say I encounter a runaway loop problem but not an error message.

Originally Posted by jimcuk

Hi Richard
I am also getting the runaway loop now, Initially I was selecting the drop down in the off hire column the vba msg box would appear I select yes, it would successfully move the row to the off hire sheet then the error would appear indicating this line

If Target.Value = "OFF_HIRE" Then

at fault

Hi,

This is slightly different to your original post in which you mentioned right clicking and deleting a row.

Right clicking a row and deleting a row of course causes the target address to be the whole row, i.e. all 16000+ columns. There is no intersection with column M in this case, hence the loop that process the upper case statement is invoked. So it's not actually a runaway ad infinitum but still takes a long time.

In getting the variable LR you're using the empty column A which always returns the value 1 which doesn't sound right.

I still can't get the error message though.