Solved Insert new row and copy data

January 5, 2013 at 10:45:17
Specs: Macintosh

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!

See More: Insert new row and copy data

Report •


✔ Best Answer
January 6, 2013 at 11:23:52

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



#1
January 5, 2013 at 11:17:56

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


Report •

#2
January 5, 2013 at 11:34:30

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)) Then

to

If UCase(Range("R" & i)) = UCase("Error") Then

The 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.


Report •

#3
January 5, 2013 at 11:53:04

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 Sub

Also, 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!


Report •

Related Solutions

#4
January 5, 2013 at 12:10:52

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.


Report •

#5
January 5, 2013 at 12:20:35

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

Report •

#6
January 5, 2013 at 14:19:59

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.


Report •

#7
January 6, 2013 at 11:23:52
✔ 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


Report •

#8
January 6, 2013 at 18:58:12

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


Report •


Ask Question