I want to search a special character in my selected range of Excel sheet and
replace it with "A". I am using code mentioned below.

Public Sub test()

Dim firstaddress As String

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
firstaddress = temp.Address
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing And temp.Address < firstaddress

End If
End Sub

It works fine till it find the requested characters. Once it didnot find
anything it returns an Runtime error 91! Any solution for this?

Thanks in advane.

Public Sub test()

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing
End If
End Sub

--
Regards,
Tom Ogilvy

"Vishal" wrote:

I want to search a special character in my selected range of Excel sheet and
replace it with "A". I am using code mentioned below.

Public Sub test()

Dim firstaddress As String

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
firstaddress = temp.Address
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing And temp.Address < firstaddress

End If
End Sub

It works fine till it find the requested characters. Once it didnot find
anything it returns an Runtime error 91! Any solution for this?

Thanks in advane.

Hi TOm,
Thanks for the reply.
After testing the first special character I am checking the other characters
with this and accorndingly I replace the data. At certain point I might leave
the data as it is. So the FindNext method will never be "Nothing". In that
case I need to check the first address of the data which was unchanged. So I
need to have the second condition for loop to continue.
Does any other solution is there except removing the first address checking?

Regards,
Vishal

"Tom Ogilvy" wrote:

Public Sub test()

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing
End If
End Sub

--
Regards,
Tom Ogilvy

"Vishal" wrote:

I want to search a special character in my selected range of Excel sheet and
replace it with "A". I am using code mentioned below.

Public Sub test()

Dim firstaddress As String

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
firstaddress = temp.Address
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing And temp.Address < firstaddress

End If
End Sub

It works fine till it find the requested characters. Once it didnot find
anything it returns an Runtime error 91! Any solution for this?

Thanks in advane.

Public Sub test()
dim sAddr as String, temp as Range
Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
Do
sAddr = temp.Address
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
if temp is nothing then exit sub
Loop While temp.Address < sAddr
End If
End Sub

--
Regards,
Tom Ogilvy


"Vishal" wrote:

Hi TOm,
Thanks for the reply.
After testing the first special character I am checking the other characters
with this and accorndingly I replace the data. At certain point I might leave
the data as it is. So the FindNext method will never be "Nothing". In that
case I need to check the first address of the data which was unchanged. So I
need to have the second condition for loop to continue.
Does any other solution is there except removing the first address checking?

Regards,
Vishal

"Tom Ogilvy" wrote:

Public Sub test()

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing
End If
End Sub

--
Regards,
Tom Ogilvy

"Vishal" wrote:

I want to search a special character in my selected range of Excel sheet and
replace it with "A". I am using code mentioned below.

Public Sub test()

Dim firstaddress As String

Range("A418", "I441").Select

Set temp = Selection.Find(what:="$", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart)

If Not temp Is Nothing Then
firstaddress = temp.Address
Do
Range(temp.Address).FormulaR1C1 = "A"
Set temp = Selection.FindNext(temp)
Loop While Not temp Is Nothing And temp.Address < firstaddress

End If
End Sub

It works fine till it find the requested characters. Once it didnot find
anything it returns an Runtime error 91! Any solution for this?

Thanks in advane.