Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Findnext | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |