B
Bob Cochran
I'm in need of some help here, and also with a macro question, too.
I need to replace text
From To
A1 A01
The series goes from A1-A32, B1-B32, ...R32 that are on many sheets
I have no problem changing the text, but can't seem to get the .FindNext to
work.
I get Compile error: Invalid or unqualified reference
on line
Set Myfound = .FindNext(Myfound)
Here is my macro (minus some code to shorten the message)
Can someone Please Help Me! Thanks in advance
Sub replaceinworkbook()
'
' replaceinworkbook Macro
' Macro recorded 10/8/2003 by rcochran
'
Dim strPin As String 'holds string to search i.e.
A1,A2...A9,B1,B2...B9,...R9
Dim n As Integer ' variable for ASCII characters Chr(n)
Dim m As Integer ' variable for incrementing 1 to 9
Dim Myfound As Range ' cell containing the text you want to search
Dim cell As String 'variable to hold found cell text
Dim firstAddress As String 'variable to keep a reference to the first
address of the Myfound
Dim n_len As Integer 'variable to count number of text characters in
Myfound
Dim m_instr As Integer 'variable count where strPin is located in Myfound
For n = 65 To 82
For m = 1 To 9
strPin = Chr(n) & m 'initialize variable to A1. Chr(65) is A, so
Chr(65) & 1 is A1
' set Myfound to the cells that are found during search
Set Myfound = Cells.Find(What:=strPin, after:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False)
If Myfound Is Nothing Then 'if strPin is not found leave If
GoTo exit_if 'jump out of If
Else
Myfound.Activate 'if strPin is found, then activate cell
firstAddress = Myfound.address
Do
cell = ActiveCell.FormulaR1C1 'set cell to Myfound
n_len = Len(cell) ' return number of characters in
cell
code removed from this message
Set Myfound = .FindNext(Myfound)
Loop While Not Myfound Is Nothing And Myfound.address <>
firstAddress
end if
exit_if:
Next m
Next n
End Sub
I need to replace text
From To
A1 A01
The series goes from A1-A32, B1-B32, ...R32 that are on many sheets
I have no problem changing the text, but can't seem to get the .FindNext to
work.
I get Compile error: Invalid or unqualified reference
on line
Set Myfound = .FindNext(Myfound)
Here is my macro (minus some code to shorten the message)
Can someone Please Help Me! Thanks in advance
Sub replaceinworkbook()
'
' replaceinworkbook Macro
' Macro recorded 10/8/2003 by rcochran
'
Dim strPin As String 'holds string to search i.e.
A1,A2...A9,B1,B2...B9,...R9
Dim n As Integer ' variable for ASCII characters Chr(n)
Dim m As Integer ' variable for incrementing 1 to 9
Dim Myfound As Range ' cell containing the text you want to search
Dim cell As String 'variable to hold found cell text
Dim firstAddress As String 'variable to keep a reference to the first
address of the Myfound
Dim n_len As Integer 'variable to count number of text characters in
Myfound
Dim m_instr As Integer 'variable count where strPin is located in Myfound
For n = 65 To 82
For m = 1 To 9
strPin = Chr(n) & m 'initialize variable to A1. Chr(65) is A, so
Chr(65) & 1 is A1
' set Myfound to the cells that are found during search
Set Myfound = Cells.Find(What:=strPin, after:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False)
If Myfound Is Nothing Then 'if strPin is not found leave If
GoTo exit_if 'jump out of If
Else
Myfound.Activate 'if strPin is found, then activate cell
firstAddress = Myfound.address
Do
cell = ActiveCell.FormulaR1C1 'set cell to Myfound
n_len = Len(cell) ' return number of characters in
cell
code removed from this message
Set Myfound = .FindNext(Myfound)
Loop While Not Myfound Is Nothing And Myfound.address <>
firstAddress
end if
exit_if:
Next m
Next n
End Sub