Left and Len problem

[danno79]

Hi all - i'm wrestling with the following problem;- I have working code;-

Dim cell As Range
Dim x As Integer

With ActiveSheet
    For x = 116 To 147
        For Each cell In .Range("M44:M75")
              If Left(cell.Value, 3) = .Range("H" & x).Value Then
                cell.ClearContents
                cell.Interior.ColorIndex = xlNone
            End If
        Next cell
    Next x

End With

But i need to modify this so that it can cope with cell.value of variable length and remove the last characer from the string something like;-

If Left(cell.Value, len(cell.value-1)) = .Range("H" & x).Value Then

But his doesn't work - any suggestions please?

 

[Andrew Poulsom]

Do you mean?

If Left(cell.Value, Len(.Range("H" & x).Value)) = .Range("H" & x).Value Then

 

[Ruddles]

If Left(cell.Value, len(cell.Value[COLOR=red][B])-1[/B][/COLOR]) = .Range("H" & x).Value Then

You were subtracting 1 from cell.Value and then finding the length of cell.Value-1; you should have been finding the length of cell.Value and then subtracting 1 from the length.

 

[danno79]

Do you mean?

If Left(cell.Value, Len(.Range("H" & x).Value)) = .Range("H" & x).Value Then

Thanks Andrew thats cracked it!!