Thread
:
Getting error trying to replace characters in a cell
View Single Post
#
2
Posted to microsoft.public.excel.programming
[email protected]
external usenet poster
Posts: 24
Getting error trying to replace characters in a cell
Thanks Dave,
I found this little snippet that did the trick, think it was Tom Ogilve
Dim sStr As String, cell As Range
Count = Range("A65000").End(xlUp).Row
Range("A2:A" & Count).Select
For Each cell In Selection
sStr = cell
sStr = Application.Substitute(sStr, ",,", ",")
cell.Value = sStr
Next
Raymond
Dave Peterson wrote:
I put this in a few cells:
=rept("asdf,,",5000)
and converted to text.
My strings were 30,000 characters long
I ran the macro and didn't get the error message.
wrote:
Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text
Raymond
Dave Peterson wrote:
Modified from a previous post:
Option Explicit
Sub testme01()
Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String
BeforeStr = ",,"
AfterStr = ","
With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0
If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If
With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows
Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub
If you're using xl97, change that Replace() to application.substitute()
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ps. Try it against a copy of your data--just in case!
wrote:
Hi,
Using Excel 2000
Example
In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................
After R588 there are 2 commas where there should only be 1, this is how
the data is extracted from our quotes system and nothing can be done
about it
Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)
Anybody know of any function which will do the required task without
receiving the error
Thanks in advance
Raymond Allan
--
Dave Peterson
--
Dave Peterson
Reply With Quote
[email protected]
View Public Profile
Find all posts by
[email protected]