Can't get findnext property of range class error
- by Lawrence Knowlton
I am trying to parse a report in Excel 2007. It is basically a report of accounting charge exceptions. The report has sections with a header for each type of exception. There are types of exceptions that are deleted from the report. I'm using a Do While loop to find each header and if the section needs to be deleted I have it do so. If nothing needs to be deleted the code works fine, but right after a section is deleted I get an "Unable to get the FindNext property of the Range Class" error. Here is my code:
Sub merge_All_Section_Headers()
' Description:
' The next portion macro will find and format the Tranaction Source rows in the file
' by checking each row in column A for the following text: TRANSA. If a cell
' has this text in it, it is selected and a function called merge_text_cells
' is run, which performs concatenation of each Transaction Source header row and
' deletes the text from the rest of the cells with broken up text.
'
lastRow = ActiveSheet.UsedRange.Rows.Count + 1
Range(lastRow & ":" & lastRow).Delete
ActiveSheet.PageSetup.Orientation = xlLandscape
With ActiveSheet.Range("A:A")
Dim searchString As String
searchString = "TRANSA"
'The following sets stringFound to either true or false based on whether or not
'the searchString (TRANSA) is found or not):
Set stringFound = .Find(searchString, LookIn:=xlValues, lookat:=xlPart)
If Not stringFound Is Nothing Then
firstLocation = stringFound.Address
Do
stringFound.Select
lastFound = stringFound.Address
merge_Text_Cells
If ((InStr(ActiveCell.Text, "CHARGE FILER") = 0) And _
(InStr(ActiveCell.Text, "CREDIT FILER") = 0) And _
(InStr(ActiveCell.Text, "PA MIDNIGHT FINAL") = 0) And _
(InStr(ActiveCell.Text, "BAD DEBT TURNOVER") = 0)) Then
section_Del 'Function that deletes unwanted sections
End If
Range(lastFound).Select
Set stringFound = .FindNext(stringFound)
Loop While Not stringFound Is Nothing And stringFound.Address <> firstLocation
End If
End With
Like I said it works fine when the section_Del is commented out. Any ideas as to how to remedy this would be greatly appreciated. Thanks!
