I can only recreate your problem if the first row contains data starting with
If you step through your code you will notice that the first instance of "Br" found is always row 2 or later. However
MSDN says:When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
As a result of that wrap-around the variable
ends up with a negative value and ... Bang! Your error is raised.
You've attempted to address this with
If fnd Is Nothing Or fnd.Address = first.Address Then Exit Do
But as the
wraps around the
is actually before
There are a couple of ways you can address the problem. You could ensure that the first row of your data does not match "Br " - e.g. by adding a header or blank row.
Alternatively, change the test to
If fnd Is Nothing Or fnd.Row <= first.Row Then Exit Do
With the sample data shown the line
fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert
will still produce the error if
is any value > 9 as the parameter then passed to
is negative or zero (an illegal value).
It's not entirely clear what you are trying to do with that line of code - if you are trying to insert a set of rows before each row that begins "Br " then you need to revisit your logic on how to calculate the number of rows to insert.