"Run-time error '1004' on code to remove the ellipsis (...) character from a column

[1819]

I am getting a "Run-time error '1004': Application-defined or object-defined error" in this code at the line indicated.

Presumably something's wrong with the formula, but I cannot see what it is.

All it is doing is removing the ellipsis (...) character from cells in column C.

Variables were declared earlier in the sub.

Please could you help.



With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("c1:c" & LastRow).Formula = "=TRIM(SUBSTITUTE(C1,(Chr(133)),""))" <<<<<THIS LINE HIGHLIGHTED BY THE DEBUGGER
.Range("c1:c" & LastRow).Copy
.Range("c1:c" & LastRow).PasteSpecial xlValues
Application.CutCopyMode = False
End With

[SamT]

I don't think "..." in a cell is an single ellipsis character. I think it is 3 "." in a row

Replace (Chr(133)) with "..."

[1819]

I don't think "..." in a cell is an single ellipsis character. I think it is 3 "." in a row

Replace (Chr(133)) with "..."

Thanks Sam. I tried that - it gave the same error. Should I post a sample worksheet?

[mancubus]

circular reference?

[1819]

circular reference?

So do you think it would work better with a helper column?

So the trimmed values would appear in the helper column and then get copied back as values over the original data?

[Paul_Hossler]

I'm confused

Since you're already running a macro why mess with WS formulas (esp since it's wrong)??

Why not use ...



myRange.Replace What:=Chr(133), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False




In this ...




.Range("c1:c" & LastRow).Formula = "=TRIM(SUBSTITUTE(C1,(Chr(133)),""))"


Chr is the VBA equivalent of the ws CHAR()

Without testing, you probably meant something like



.Range("c1:c" & LastRow).Formula = "=TRIM(SUBSTITUTE(CHAR(133),""))"

[1819]

[QUOTE=Paul_Hossler;355699]



myRange.Replace What:=Chr(133), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Yes, Paul, you are absolutely right and in fact I had tried that code and it would not work either.

I only resorted to a WS formula because when I put it in the neighbouring column it produced the right result.

I had also tried CHAR instead of CHR.

Your untested
.Range("c1:c" & LastRow).Formula = "=TRIM(SUBSTITUTE(CHAR(133),""))"

does not work.

Grateful for any further ideas, especially along the lines of mancubus' thought that there's a circular reference.

[SamT]

This creates a circular reference in C1
.Range("c1:c" & LastRow).Formula = "=TRIM(SUBSTITUTE(C1,(Chr(133)),""))"

This does not
.Range("C2:C" & LastRow).Formula = "=TRIM(SUBSTITUTE(C1,(Chr(133)),""))"

But that may not be doing what you think it is doing.
What it is doing is setting the values of every cell in column C to whatever is in C1, trimmed and edited, of course.

If that is what you want then, in VBA, something like


Dim Blah as String
Blah = Range("C1").Replace What:=Chr(133), Replacement:=""
.Range("c1:c" & LastRow) = Blah

[1819]

Thanks for your help everyone. I've managed to get this working:



With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("e1:e" & LastRow).Formula = "=TRIM(SUBSTITUTE(RC[-2],CHAR(133),"" ""))"
.Range("e1:e" & LastRow).Copy
.Range("e1:e" & LastRow).PasteSpecial xlValues
Application.CutCopyMode = False
End With


I know it's not the most elegant or efficient solution but it seems to work.

[Paul_Hossler]

Yes, Paul, you are absolutely right and in fact I had tried that code and it would not work either.
Grateful for any further ideas, especially along the lines of mancubus' thought that there's a circular reference
.


I'm surprised it didn't work for you.

This works for me and replaces all the ... in Col C




Option Explicit

Sub AAA()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ' Col A
' Col C
.Range("c1:c" & LastRow).Replace What:=Chr(133), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

End Sub

[1819]

This works for me and replaces all the ... in Col C




Option Explicit

Sub AAA()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ' Col A
' Col C
.Range("c1:c" & LastRow).Replace What:=Chr(133), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

End Sub



Paul, thanks. You are totally right - your code works. It's a lot cleaner than my workaround in comment #9.