Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
No luck, it just leaves the cells with an "*" alone like my other code.
"Vergel Adriano" wrote in 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 "Roger" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
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 "Roger" wrote: No luck, it just leaves the cells with an "*" alone like my other code. "Vergel Adriano" wrote in 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 "Roger" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
another approach
If InStr(1, c, "*") 0 Then c.ClearContents -- Gary "Roger" wrote in message ... No luck, it just leaves the cells with an "*" alone like my other code. "Vergel Adriano" wrote in 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 "Roger" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
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 "Roger" wrote: No luck, it just leaves the cells with an "*" alone like my other code. "Vergel Adriano" wrote in 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 "Roger" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find * in a cell & ClearContents
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 "Ron Coderre" wrote in message ... 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 "Roger" wrote: No luck, it just leaves the cells with an "*" alone like my other code. "Vergel Adriano" wrote in 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 "Roger" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining find with clearcontents on multiple columns | Excel Programming | |||
clearcontents, worksheet_change, cell validation | Excel Programming | |||
Clearcontents | Excel Programming | |||
Clearcontents | Excel Programming | |||
Clearcontents | Excel Programming |