search and replace loop problem

[Ken]

I am using the following code to replace all letters from a worksheet

Sub deletetest()

Dim ws As Worksheet
Dim Rep As String

Set ws = ActiveSheet

For i = 65 To 90

Rep = Chr(i)

Debug.Print Rep

ws.Cells.Replace What:=Rep, Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Next

End Sub

For some reason it won't replace the H's. My immediate window shows
that Rep was H when i was 52. If I change the code to manually make
Rep="H" it replaces the H's. If I run search and replace on the
worksheet for H it replaces the H's; it is just the H in the loop that
it seems to skip.

Can someone tell me what is happening?

Thanks

Ken

 

[Paul Robinson]

Hi
How can i be 52 when the loop goes from 65 to 90??
regards
Paul

 

[Don Guillett Excel MVP]

I don't understand what you are doing or why you are doing it THAT way
but if you have formlas you would have a problem. So add


On Error Resume Next

before your for

 

[Ken]

Paul

Good point, that was a typo, when i is 72, Char(i) is H and for some
reason, the H's do not get replaced; but all the other letters on the
worksheet are gone. All the letters were gone except the H's and the
=CHAR(*) formulas were turned into =H(*); consistent with my loop not
deleting any H's. When I add a special line of code to delete the H's

ws.Cells.Replace What:="H", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

after the loop, the H's are also removed.

After having all my formulas messed up several times in testing, I got
a little smarter and started my testing on sheet2, where I did not
have any formulas, but where I pasted the alphabet generated from the
=CHAR() formulas on sheet1. When I run the code there, all the
letters are gone except for the I's. Now I am even more confused. To
make it worse, I ran it on sheet3 and all the letters were eliminated.

Any ideas?

Thanks
Ken

 

[Ken]

Don

That took care of it, thanks.

It was definitely a problem with formulas, but, I don't understand
exactly how it picked the letters to not replace. Subsequent
troubleshooting on sheet2 caused it to not delete any i's, and running
it again on sheet3 did not have any problems, all the letters were
gone. Apparently it was okay changing CHAR to CHR and then changing
CHR to HR, but when it came ot changing HR to R it quit after changing
the formulas and did not replace any more H's. On sheet2 I had an IF
formula and it changed to =F(... and then stopped replacing i's. My
third sheet had no formulas, hence,no problems.

Do you know of a better way to replace all the letters in a worksheet?

Thanks

Ken

 

[Don Guillett Excel MVP]

Don

That took care of it, thanks.

It was definitely a problem with formulas, but, I don't understand
exactly how it picked the letters to not replace.  Subsequent
troubleshooting on sheet2 caused it to not delete any i's, and running
it again on sheet3 did not have any problems, all the letters were
gone.  Apparently it was okay changing CHAR to CHR and then changing
CHR to HR, but when it came ot changing HR to R it quit after changing
the formulas and did not replace any more H's.  On sheet2 I had an IF
formula and it changed to =F(... and then stopped replacing i's.  My
third sheet had no formulas, hence,no problems.

Do you know of a better way to replace all the letters in a worksheet?

Thanks

Ken

n Jan 29, 2:08 pm, Don Guillett Excel MVP <[email protected]>
wrote:








- Show quoted text -

I still can't figure out what you are trying to do. Do you want to
replace all words not in a formula.
I would need to see your file and before/after examples

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Don Guillett Excel MVP]

I still can't figure out what you are trying to do. Do you want to
replace all words not in a formula.
I would need to see your file and before/after examples

"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -

Maybe?? this is what you want. It will clear all cells that are text
OR text with numbers
Sub NoLetters()
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues).ClearContents
End Sub

 

[Ken]

Don
Actually, the guy I am trying to help wants to leave the numbers that
are part of alpha-numeric text strings; I don't know why. That is why
I opted to loop through the alphabet.
Thanks for getting me straightened out on the original problem.
Ken