Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can anyone see why this throws up a 1

Status
Not open for further replies.

jaydeebe

IS-IT--Management
Nov 14, 2003
169
GB
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").Select
Application.CutCopyMode = False
Selection.Copy
Range("f2:f2000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F2:F2000").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
 
COuld it be something to do with the ranges.

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

dyarwood
 
It is the same sorry. Just had the wrong thing in the code I tried sorry
 
Could it be something to do with the LEN function. Might that be expecting to be put into a variable?

dyarwood
 
error 5 invalid procedure call or argument
 
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
 
last but one, "cell.value ect..."
 
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
 
yes, can the next be stopped on a null value
 
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
 
WICKED, thanks v.much, that works perfectly minus the second end if!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top