• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is this possible IF(Not IsError())

T

Tim Hanson

Member
Trying to error handle cells that have more the 255 char
Thanks

Code: 
Sub TrimSheet()
Dim aCell as Range

For Each aCell In ActiveSheet.UsedRange.Offset(1, 0)
          aCell.Value = Evaluate("IF(Not IsError(ROW(" & aCell.Address & "),CLEAN(TRIM(" & aCell.Address & "))))")

End Sub
 
Hi Tim ,

When you use the Evaluate function , what you are doing is evaluating an Excel worksheet formula , with the formula written the way you would if you were to enter it in a worksheet cell.

Thus , the NOT worksheet function needs to have its parameter enclosed in parentheses.

As a general rule , what I do is assign the value of the formula which is to be used within the Evaluate function , to a string variable , and then display this string variable in the Immediate window. What you see should be identical to what you would have entered in a worksheet cell. If what you see is not identical , then the evaluation is not going to work.

Add two lines such as :

strvar = "=IF(Not (IsError(ROW(" & aCell.Address & ")),CLEAN(TRIM(" & aCell.Address & "))))"

Debug.Print strvar

and see what is displayed.

Narayan
 
You must log in or register to reply here.
Back
Top