Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Dear All,
we have a pricing template that some turkeys decide to include text in it when filling it out. How can I find and replace ANY text that appears in the range? The variance of entered text is huge, so there is no way to find and replace specific text. We want to replace any text with a zero ("0"). Basically, we need to go from cell to cell, find an instance of text, then replace it with zero. Help would be greatly appreciated... Regards, andym |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Hi,
The following will replace any cell which has non-numeric data with 0; this will include data such as ABC123 or 12AB45 etc. which will be set to 0 not 0123 or 12045. Is this what you want? It looks as though you need to add code which checks for numeric-only input to avoid a repeat of this problem. Sub ReplaceTextWithZero() Dim rng As Range, cell Set rng = Range("a1:Z1000") ' <==== Replace as required For Each cell In rng If Not IsNumeric(cell) Then cell.Value = 0 End If Next cell End Sub HTH " wrote: Dear All, we have a pricing template that some turkeys decide to include text in it when filling it out. How can I find and replace ANY text that appears in the range? The variance of entered text is huge, so there is no way to find and replace specific text. We want to replace any text with a zero ("0"). Basically, we need to go from cell to cell, find an instance of text, then replace it with zero. Help would be greatly appreciated... Regards, andym |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Further to my previous note:
Date strings such as 12/05/2005 will also appear as 00/00/0000. So you may need to add further tests to check if field is a date [or other formats] where my approach is TOO simple. " wrote: Dear All, we have a pricing template that some turkeys decide to include text in it when filling it out. How can I find and replace ANY text that appears in the range? The variance of entered text is huge, so there is no way to find and replace specific text. We want to replace any text with a zero ("0"). Basically, we need to go from cell to cell, find an instance of text, then replace it with zero. Help would be greatly appreciated... Regards, andym |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
This doesn't make any attempt to establish validity - if it is a hard coded
text value in the range, it is replaced with zero. Sub ReplaceText() Dim rng as Range On Error Resume Next set rng = Range("B2:Z26") rng.specialcells(xlconstants,xlTextValues).Value = 0 On Error goto 0 End Sub -- Regards, Tom Ogilvy wrote in message oups.com... Dear All, we have a pricing template that some turkeys decide to include text in it when filling it out. How can I find and replace ANY text that appears in the range? The variance of entered text is huge, so there is no way to find and replace specific text. We want to replace any text with a zero ("0"). Basically, we need to go from cell to cell, find an instance of text, then replace it with zero. Help would be greatly appreciated... Regards, andym |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Tom, if the text value is a number, such as an account number, how can
I amend the code to change the text value to an actual number. TIA Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Then you have to loop
Sub ReplaceText() Dim rng as Range Dim rng1 as Range Dim cell as Range set rng = Range("B2:Z26") On Error Resume Next set rng1 = rng.specialcells(xlconstants,xlTextValues) On error goto 0 if not rng1 is nothing then rng1.Numberformat:="General" for each cell in rng1 if isnumeric(cell) then cell.formula = cell.Value else cell.Value = 0 end if Next end if End Sub -- Regards, Tom Ogilvy "GregR" wrote in message ups.com... Tom, if the text value is a number, such as an account number, how can I amend the code to change the text value to an actual number. TIA Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text and replacing in VBA
Thanks Tom
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding and replacing text with a blank | Excel Discussion (Misc queries) | |||
finding and replacing | Excel Worksheet Functions | |||
Finding and Replacing | Excel Discussion (Misc queries) | |||
Finding, Replacing and Cut & Paste | Excel Worksheet Functions | |||
Finding and Replacing a "?" | Excel Discussion (Misc queries) |