Hi all,

New to macros and trying to write one to scroll through my data looking for rows with "ERROR" in column R. I'd then like to to insert a new row beneath this found row, copy some data down (but not all), and then continue on to the next "ERROR" marker until the end. Any help would be appreciated!

✔ Best Answer

Ok - that leaves about 20 minutes a day for coding. Here is the updated code with correction and stub for additional code to populate the empty row.

Sub FindError() 'Find the last populated cell in Col R LastRow = Range("R" & Rows.Count).End(xlUp).Row 'loop through populated cell in Col R For I = 1 To LastRow If UCase(Range("R" & I)) = UCase("Error") Then 'Insert a row after the Error Range("R" & I + 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Populate the new row with data should ensure that it won't = "ERROR" Range("R" & I + 1) = "Data from somewhere" i = i +1 End If Next End Sub

Here's a start. Sub FindError() 'Find the last populated cell in Col R LastCol = Range("R" & Rows.Count).End(xlUp).Row 'loop through populated cell in Col R For i = 1 To LastCol If Application.IsError(Range("R" & i)) Then 'Finds cells in R that have an Error result 'Insert a row after the Error Range("R" & i + 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Do whatever else you need to do with the new Row End If Next End Sub

Are looking to find the text value ERROR or are you looking to find an Error returned by Excel such as #N/A, #VALUE, #NAME, etc? If you are searching for the text value ERROR, change this line in AlteK's fine code from:

If Application.IsError(Range("R" & i)) Thento

If UCase(Range("R" & i)) = UCase("Error") ThenThe UCase (UpperCase) function is simply to ensure that any version of ERROR (Error, error, ErRoR, etc.) is found.

Note: My suggestion will not find Errors returned by Excel. AlteK's original code does that. Of course, both methods could be used with an OR function so that both types of errors would be found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for the quick replies! It is a text value ERROR. What I have so far does insert rows, but at the top of the file and not underneath the appropriate row. I'm basing it off a code that was meant to do something else, so there may be some unnecessary code in there (I'm not sure the second If statement is needed)... Sub newrow() Dim r As Integer eRow = InputBox("what is your last row of data?") For r = 2 To eRow If Sheets("allyears").Cells(r, 18) = "ERROR" Then Cells(r + 1).EntireRow.Insert r = r + 1 End If If Sheets("allyears").Cells(r, 18) = "" Or Sheets("allyears").Cells(r, 18) = "FALSE" Then GoTo nextg End If nextg:: Next r End SubAlso, when I want to add in under the 'insert new row' line code for copying certain cells down, would it look something like this?: Cells(r+1,1).Value = Cells(r,1).Value

Thanks!

Altek's base code with my modification is much better than the code you posted since Altek provides the code to determine the last line of data in Column R without the need for user interaction. 'Find the last populated cell in Col R LastCol = Range("R" & Rows.Count).End(xlUp).Row(Although I might have used LastRow, instead of LastCol, but that's just a nit)

I think it would help if we knew what cells you wanted to copy.

Cells(r+1, 1).Value = Cells(r, 1).Value will "copy" just the value in Column A and it would cumbersome to use code like that for each cell to be copied. There may be a more efficient way to do it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Agreed - LastRow would be more appropriate. In my defence - it was very early in the morning when I wrote that up.

Sorry....bleary eyed, hungover, or sleepy is no excuse. From now on, no more coding until you've had at least 2 cups of coffee. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ok - that leaves about 20 minutes a day for coding. Here is the updated code with correction and stub for additional code to populate the empty row.

Sub FindError() 'Find the last populated cell in Col R LastRow = Range("R" & Rows.Count).End(xlUp).Row 'loop through populated cell in Col R For I = 1 To LastRow If UCase(Range("R" & I)) = UCase("Error") Then 'Insert a row after the Error Range("R" & I + 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Populate the new row with data should ensure that it won't = "ERROR" Range("R" & I + 1) = "Data from somewhere" i = i +1 End If Next End Sub

Hi John Thanks for marking this Solved however there is a bug in the code - it doesn't take into account the number of additional rows created and if there are any "ERROR" cells late in the list they may not be accounted for. I resolved this by predetermining the number of "ERRORS" and adding them to the loop. Here is the updated code....

Sub FindError() 'Find the last populated cell in Col R LastRow = Range("R" & Rows.Count).End(xlUp).Row AddRows = Application.CountIf(Range("R1:R" & LastRow), "ERROR") 'loop through populated cell in Col R For I = 1 To LastRow + AddRows If UCase(Range("R" & I)) = UCase("Error") Then 'Insert a row after the Error Range("R" & I + 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Populate the new row with data - should ensure that it won't = "ERROR" Range("R" & I + 1) = "Data from somewhere" I = I + 1 End If Next End Sub

Ask Your Question

Weekly Poll

How concerned are you about the OpenSSL "Heartbleed" bug?

Discuss in The Lounge

Poll History