Search &, replace several values

Posted by amroo on May 17, 2001 11:14 PM

Bonjour, I have this code:
'""""""""""""""""""""""""""""""""""""""""""""""""""""
Range("B10:G51").Replace What:="#N/A",Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Range("B10:G51").Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Range"B10:G51").Replace ,what:="#DIV/!", ",LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=True
'""""""""""""""""""""""""""""""""""""""""""""""""""""""
is there a way to write it as this:
Range("B10:G51").Replace What:="#N/A","0","#DIV/!_ Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=True
Thanks for all
A+mroo

Posted by Dave Hawley on May 18, 2001 3:47 AM

Hi Amroo

You could use a small Loop for this like below but the speed difference may be nothing.

Dim sFind As String
Dim i As Integer
i = 1
sFind = "#N/A"
On Error Resume Next
For i = 1 To 3
Range("B10:G51").Replace What:=sFind _
, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
If i = 1 Then sFind = "0" Else: sFind = "#DIV*"
Next i

Dave

OzGrid Business Applications