vb shortening the value of a cell

[asylum]

Hi,

Can you tell me what is wrong with the below? I am trying to remove the far right character from the values in any cell in the range, but it keeps saying i have an invalid proceedure call or argument at:R.Value = Left(R.Value, l)

Thanks



Sub shorten()

Dim R As Range
Dim l As Integer

For Each R In Range("A1:a300")

l = Len(R.Value) - 1

R.Value = Left(R.Value, l)

Next R

End Sub

 

[AlphaFrog]

If r is an empty cell, I wiil be -1 and that could cause an error.

 

[VoG]

That works for me. Do you have a Sub called Left by any chance?

 

[Yard]

You have defined your variable l as an Integer, but Left() requires a Long value.

Change

Dim l As Integer

to

Dim l As Long

 

[AlphaFrog]

If R is an empty cell, l wiil be -1 and that could cause an error.

Maybe try something like this...

Sub shorten()

Dim R As Range

For Each R In Range("A1:a300")

If Len(R.Value) > 1 Then R.Value = Left(R.Value, Len(R.Value) - 1)

Next R

End Sub

 

[Yard]

I think AlphaFrog has it though.

Try

If l > 0 Then
R.Value = Left(R.Value, l)
End If

 

[Ruddles]

Try:-

If l >= 0 Then R.Value = Left(R.Value, l)

 

[asylum]

Wow,

Thanks very much guys for all the quick responses, yes you all had goo dvariations on teh theme that I had forgotten to allow for exceptions (such as the cell being blank!).

once again simple when shown how! - cheers

Andy