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

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

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

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

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

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

Finding text and replacing in VBA
 

Thanks Tom