Issue with #NAME? in my Excel VBA

Hello,

When I run my macro in Excel, to bring CSV data into Excel and run my calcs I get run time error 13. "Type mismatch"

I get it on this line:   If MyLen > 4000 Then

It's due to #NAME? error that I get from the column with Notes (text field).
I need to insert "skip-that-error" into my Sub.

Can you please help?

Thanks!





Sub TrimNotes()

    Dim RemString, MyLen, NewString
    Dim Rng, MyCell As Range
    Set Rng = Range("G:G") 'Change range to suit
    For Each MyCell In Rng
        RemString = MyCell.Value
        MyLen = Len(RemString)
        If MyLen > 4000 Then
            NewString = Left(RemString, 4000) 'Change value (length of string) to suit
            MyCell.Value = NewString
        End If
    Next
End Sub

Open in new window

CABRLU63Asked:
Who is Participating?
 
Rory ArchibaldCommented:
You could replace this part:
    For Each MyCell In Rng
        RemString = MyCell.Value
        MyLen = Len(RemString)
        If MyLen > 4000 Then
            NewString = Left(RemString, 4000) 'Change value (length of string) to suit
            MyCell.Value = NewString
        End If
    Next

Open in new window

with this:
    For Each MyCell In Rng
       If not Iserror(Mycell.value) then
        RemString = MyCell.Value
        MyLen = Len(RemString)
        If MyLen > 4000 Then
            NewString = Left(RemString, 4000) 'Change value (length of string) to suit
            MyCell.Value = NewString
        End If
    End If
    Next

Open in new window

0
 
Rodney EndrigaData AnalystCommented:
Did you try insertting 'On Error Resume Next'?

You can insert this line as follows:
...
On Error Resume Next
 For Each MyCell In Rng
...

Not sure if this is what you were looking for.
0
 
Rob HensonFinance AnalystCommented:
Not for points as Rory has done the work.

In addition, I know it is good to define variables but you could reduce the routine by a couple of lines as the definitions aren't really required, with Rory's IsError suggestion:

For Each MyCell In Rng
  If not Iserror(Mycell.value) then
       If Len(MyCell.Value) > 4000 Then MyCell.Value = Left(MyCell.Value, 4000) 'Change value (length of string) to suit
  End If
Next

Open in new window

Thanks
Rob
0
 
CABRLU63Author Commented:
Both are GREAT solutions, thank you for PROMT response.
And tested botof them they are only few seconds apart in rendering final results. 1:23 min vs 1:27.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.