Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 461-Method or data member not found
I've tried to tweek this code from Ron de Bruin www.rondebruin.com but the
code stops at the .Find after Set rngFound = Can anyone help me understand why, and how to fix it? Thanks in advance! Sub Example() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim rngFound As Range Dim strFirstAddress As String Dim strFind As String Dim strReplace As String Dim wsInput As Worksheet Dim ErrorYes As Boolean Dim floc As Range Set wsInput = Worksheets("Input") strFind = wsInput.Range("B8").Text strReplace = wsInput.Range("B9").Text floc = wsInput.Range("B5") 'Fill in the path\folder where the files are MyPath = "F:\EHP IBNR\Trend Automation\Testing\Trend Testing\" & floc 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*Variances*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Find(What:=strFind, LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 461-Method or data member not found
You are in a with clause for the sheet sh so what you have for your find is
essentially Set rngFound = sh.Find(What:=strFind, ... The problem is sheets do not have a find method. Ranges do. Since I assume you are searching the entire sheet you could do this Set rngFound = .Cells.Find(What:=strFind, ... -- HTH... Jim Thomlinson "shorticake" wrote: I've tried to tweek this code from Ron de Bruin www.rondebruin.com but the code stops at the .Find after Set rngFound = Can anyone help me understand why, and how to fix it? Thanks in advance! Sub Example() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim rngFound As Range Dim strFirstAddress As String Dim strFind As String Dim strReplace As String Dim wsInput As Worksheet Dim ErrorYes As Boolean Dim floc As Range Set wsInput = Worksheets("Input") strFind = wsInput.Range("B8").Text strReplace = wsInput.Range("B9").Text floc = wsInput.Range("B5") 'Fill in the path\folder where the files are MyPath = "F:\EHP IBNR\Trend Automation\Testing\Trend Testing\" & floc 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*Variances*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Find(What:=strFind, LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 461-Method or data member not found
On further examination you are going to have more problems. You .Replace and
..FindNext also need to be fixed something like this... (note that I have not examined your code to determine what it is supposed to do so what I post here may not do exactly what you want it to do) 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Cells.Find(What:=strFind, _ LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Cells.Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .Cells.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You are in a with clause for the sheet sh so what you have for your find is essentially Set rngFound = sh.Find(What:=strFind, ... The problem is sheets do not have a find method. Ranges do. Since I assume you are searching the entire sheet you could do this Set rngFound = .Cells.Find(What:=strFind, ... -- HTH... Jim Thomlinson "shorticake" wrote: I've tried to tweek this code from Ron de Bruin www.rondebruin.com but the code stops at the .Find after Set rngFound = Can anyone help me understand why, and how to fix it? Thanks in advance! Sub Example() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim rngFound As Range Dim strFirstAddress As String Dim strFind As String Dim strReplace As String Dim wsInput As Worksheet Dim ErrorYes As Boolean Dim floc As Range Set wsInput = Worksheets("Input") strFind = wsInput.Range("B8").Text strReplace = wsInput.Range("B9").Text floc = wsInput.Range("B5") 'Fill in the path\folder where the files are MyPath = "F:\EHP IBNR\Trend Automation\Testing\Trend Testing\" & floc 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*Variances*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Find(What:=strFind, LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 461-Method or data member not found
Thanks soooo much!! That part works great, but now the code stops at Loop
Until rngFound.Address = strFirstAddress and I get an "Error 91 - Object variable or With block variable not set." "Jim Thomlinson" wrote: On further examination you are going to have more problems. You .Replace and .FindNext also need to be fixed something like this... (note that I have not examined your code to determine what it is supposed to do so what I post here may not do exactly what you want it to do) 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Cells.Find(What:=strFind, _ LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Cells.Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .Cells.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You are in a with clause for the sheet sh so what you have for your find is essentially Set rngFound = sh.Find(What:=strFind, ... The problem is sheets do not have a find method. Ranges do. Since I assume you are searching the entire sheet you could do this Set rngFound = .Cells.Find(What:=strFind, ... -- HTH... Jim Thomlinson "shorticake" wrote: I've tried to tweek this code from Ron de Bruin www.rondebruin.com but the code stops at the .Find after Set rngFound = Can anyone help me understand why, and how to fix it? Thanks in advance! Sub Example() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim rngFound As Range Dim strFirstAddress As String Dim strFind As String Dim strReplace As String Dim wsInput As Worksheet Dim ErrorYes As Boolean Dim floc As Range Set wsInput = Worksheets("Input") strFind = wsInput.Range("B8").Text strReplace = wsInput.Range("B9").Text floc = wsInput.Range("B5") 'Fill in the path\folder where the files are MyPath = "F:\EHP IBNR\Trend Automation\Testing\Trend Testing\" & floc 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*Variances*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in ALL worksheets in mybook On Error Resume Next For Each sh In mybook.Worksheets If sh.ProtectContents = False Then With sh wsInput.Activate Set rngFound = .Find(What:=strFind, LookIn:=xlFormulas, _ LookAt:=xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound = .Replace(What:=strFind, Replacement:=strReplace, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ ReplaceFormat:=True) sh.Activate Set rngFound = .FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If End With Else ErrorYes = True End If Next sh If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile error: Method or data member not found | Excel Worksheet Functions | |||
Compile error: Method or data member not found | Excel Programming | |||
Compile Error Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming |