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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.