Hi, the "left" is creating error. I am trying to delete "cu" if it represents
the first two characters in the cell.

Sub Step8_CleanUpAllSheet()

Dim r As Integer


For r = Cells(65536, 4).End(xlUp).Row To 2 Step -1


If Left(Cells(r, 4), 2) = "cu" Then
Cells(r, 4).TextToColumns Destination:=Cells(r, 4),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(2, 1)),
TrailingMinusNumbers:=True
End If
Next r


End Sub

In the VB Editor Click on Tools - References. Look for anything that is
checked but indicates Missing. This is actually where your run time error is
coming from. It actually has nothing to do with the Left fucntion as you
might imagine (One of the quirks of VBA).

Chances are you can just uncheck the reference and your code will run with
no problems. If after unchecking however your code no longer compiles then
you need to go looking for a versio of the missing reference that does exist
on your system and add that in.
--
HTH...

Jim Thomlinson


"Tony D" wrote:

Hi, the "left" is creating error. I am trying to delete "cu" if it represents
the first two characters in the cell.

Sub Step8_CleanUpAllSheet()

Dim r As Integer


For r = Cells(65536, 4).End(xlUp).Row To 2 Step -1


If Left(Cells(r, 4), 2) = "cu" Then
Cells(r, 4).TextToColumns Destination:=Cells(r, 4),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(2, 1)),
TrailingMinusNumbers:=True
End If
Next r


End Sub

TD,
The "TrailingMinusNumbers:=True" argument is not available in xl97 and xl2000.
Try inserting an apostrophe just ahead of the last comma in "FieldInfo:=...
Jim Cone
San Francisco, USA

"Tony D" wrote in message
...
Hi, the "left" is creating error. I am trying to delete "cu" if it represents
the first two characters in the cell.

Sub Step8_CleanUpAllSheet()
Dim r As Integer
For r = Cells(65536, 4).End(xlUp).Row To 2 Step -1
If Left(Cells(r, 4), 2) = "cu" Then
Cells(r, 4).TextToColumns Destination:=Cells(r, 4),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(2, 1)), TrailingMinusNumbers:=True
End If
Next r
End Sub