View Full Version : [SOLVED:] "Run-time error '1004' on code to remove the ellipsis (...) character from a column
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
I don't think "..." in a cell is an single ellipsis character. I think it is 3 "." in a row
Replace (Chr(133)) with "..."
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
01-27-2017, 04:33 PM
circular reference?
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
01-27-2017, 05:27 PM
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),""))"
[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.
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
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
01-28-2017, 01:21 PM
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.