Can anyone see why this throws up a 1

[jaydeebe]

Can anyone see why this throws up an error on completion. It does what i want, which is copying values from another column, removing the last two characters and finally adding a space before the last character in every cell in the column. (I know the code is wrapping on here, it doesn't in my program)

Private Sub CommandButton1_Click()
Range("e2:e2000&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("f2:f2000&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F2:F2000&quot.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 1) & " " & Right(cell.Value, 1)
Next
End Sub

 

[dyarwood]

COuld it be something to do with the ranges.

Range("e2&quot is not the same as Range("E2&quot is it?

dyarwood

 

[dyarwood]

It is the same sorry. Just had the wrong thing in the code I tried sorry

 

[RobBroekhuis]

Please be more specific about what error you're getting.


Rob

 

[dyarwood]

Could it be something to do with the LEN function. Might that be expecting to be put into a variable?

dyarwood

 

[jaydeebe]

error 5 invalid procedure call or argument

 

[xlbo]

what line does it highlight when you hit debug after the error ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jaydeebe]

last but one, "cell.value ect..."

 

[xlbo]

Any chance F2000 is blank ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jaydeebe]

yes, can the next be stopped on a null value

 

[xlbo]

just use this:

If Not cell.HasFormula Then
If len(cell.text)<> 0 then cell.Value = Left(cell.Value, Len(cell.Value) - 1) & &quot; &quot; & Right(cell.Value, 1)
end if
end if
Next


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jaydeebe]

WICKED, thanks v.much, that works perfectly minus the second end if!