CODEWITHSUNDEEP
vbaexcelruntime-error
Robby
Robby

Reputation: 827

Runtime error 13 when using Trim

I have this bit of VBA that I've used on many Excel workbooks without issues. On a particular workbook, I'm getting a Runtime Error '13': Type Mismatch error as soon as it gets to the Cell = Trim(Cell) part. What could be causing this? I've looked through the data, but can't find anything out of the ordinary that would be messing it up.

Sub TrimHS()
Application.Cursor = xlWait
Application.ScreenUpdating = False
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A83:G" & LastRow).Select
Dim rng As Range, Cell As Range
Set rng = Selection

For Each Cell In rng
Cell = Trim(Cell)

Next Cell

Application.Cursor = xlDefault

End Sub

Upvotes: 2

Views: 809

Answers (2)

Vityata
Vityata

Reputation: 43575

You have an error on a particular workbook, thus the Trim() gives error 13, if you are trying to trim it. Make sure that you have no error before trimming:

For Each Cell In rng
    If Not IsError(Cell) Then
        Cell = Trim(Cell)
    End If
Next Cell

To see what is wrong with the code, write debug.print Cell before the Trim line. It would start printing a lot at the console, the last line to print should look like this: Error 2007 or similar.

This will show the cell with the error in a MsgBox and its Worksheet:

For Each Cell In rng
    If Not IsError(Cell) Then
        Cell = Trim(Cell)
    Else
        MsgBox "Error on " & Cell.Address & " in " & Cell.Parent.Name
    End If
Next Cell

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

Cell is of type Excel.Range and Trim outputs of type String

I believe cell.value=trim(cell.value) is what you'll need

Upvotes: 2

Related Questions