Find * in a cell & ClearContents

[Roger]

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one that
has an "*" just ClearContents of that cell or better yet replace the entire
contents of that cell with "No JS #". I can't get the following code to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger

 

[Guest]

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub

 

[Roger]

No luck, it just leaves the cells with an "*" alone like my other code.

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one that
has an "*" just ClearContents of that cell or better yet replace the
entire
contents of that cell with "No JS #". I can't get the following code to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger

 

[Guest]

Hmmn, the code I gave worked for me in XL2003, I didn't think there'd be a
difference in XL2000. A different approach would be this:

Sub Replace()
For Each c In Range("n3:n100")
If instr(1, c.Text, "*") > 0 Then c.ClearContents
Next c
End Sub

No luck, it just leaves the cells with an "*" alone like my other code.

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one that
has an "*" just ClearContents of that cell or better yet replace the
entire
contents of that cell with "No JS #". I can't get the following code to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger

 

[Gary Keramidas]

another approach

If InStr(1, c, "*") > 0 Then c.ClearContents


--


Gary

No luck, it just leaves the cells with an "*" alone like my other code.

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one that
has an "*" just ClearContents of that cell or better yet replace the entire
contents of that cell with "No JS #". I can't get the following code to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger

 

[Guest]

Try something like this code which finds all cells in N3:N100 that contain an
asterisk and replaces their contents with "No JS #":

Sub AlterAsteriskCells()
Range("N3:N100").Replace _
What:="*~**", _
Replacement:="No JS #", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

No luck, it just leaves the cells with an "*" alone like my other code.

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one that
has an "*" just ClearContents of that cell or better yet replace the
entire
contents of that cell with "No JS #". I can't get the following code to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger

 

[Roger]

Thanks, the last three of the recommendations worked exactly as I needed.

Thanks for the help, it is much appreciated, have a great day all.

Roger

Try something like this code which finds all cells in N3:N100 that contain
an
asterisk and replaces their contents with "No JS #":

Sub AlterAsteriskCells()
Range("N3:N100").Replace _
What:="*~**", _
Replacement:="No JS #", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

No luck, it just leaves the cells with an "*" alone like my other code.


message

Roger,

Try it this way:

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "[*]" Then c.ClearContents
Next c
End Sub





:

XL2000

I have a column of data, formatted as General, alpha numeric only, no
formulas. I want the macro to search each cell and when it finds one
that
has an "*" just ClearContents of that cell or better yet replace the
entire
contents of that cell with "No JS #". I can't get the following code
to
work. Anybody have any better ideas?

Sub Replace()
For Each c In Range("n3:n100")
If (c.Value) Like "~*" Then c.ClearContents
Next c
End Sub

Examples of the column of data look like:
Col. N
----------
*D*0704
P1449F0
*D*0704
P1449F0
*D*0703
*D*0703


Thanks,

Roger