Conditional Inserting using VBA

Question

I was trying to insert a row just below another row base on some condition. Now this code will loop through many sheets. It work fine for 1 sheet, but when it tries to perform the same function for next sheet, it showing error. Can anybody help me to solve the problem. The code I was using is given below:

Sub test()
Dim a As Worksheet
Dim lngRow As Long
Dim s As String
Dim z As Variant
s = "Sheet1,Sheet2"
z = VBA.Split(s, ",")
For Each i In z
Set a = Sheets(i)
For lngRow = a.UsedRange.Rows.Count To 1 Step -1
If UCase$(a.Cells(lngRow, 2).Value) = "R" Then
a.Range("A" & CStr(lngRow + 1)).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next lngRow
Next i
End Sub

The error is coming in this row:

a.Range("A" & CStr(lngRow + 1)).Select

chris neilsen · Accepted Answer · 2012-11-17 19:42:35Z

2

The reason you get an error is that you can only Select a range on the Active sheet. To fix your code as is, add a.Activate before the For loop.

Better still, don't Select at all.

answered Nov 17, 2012 at 19:42

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Apostolos55 · Accepted Answer · 2012-11-19 09:32:02Z

1

your code assumes that Sheets(1) equals sheets("Sheet1") which might not be always the case. better use a code like the following:

Sub Test1()
Dim aSht As Variant, SafeShts As String
Dim lngrow As Long

SafeShts = LCase("Sheet3 , Sheet4")   'Write here sheets to be unaffected

For Each aSht In ThisWorkbook.Sheets
    If InStr(SafeShts, LCase(aSht.Name)) < 1 Then   'Current sheet not within safe sheets
        For lngrow = aSht.UsedRange.Rows.Count To 1 Step -1
            If UCase(aSht.Cells(lngrow, 2)) = "R" Then
                aSht.Cells(lngrow + 1, 1).EntireRow.Insert xlShiftDown, xlFormatFromLeftOrAbove
            End If
        Next
    End If
Next

End Sub

The code doesn't select anything before changes, so it is pretty fast to.

Cheers

answered Nov 19, 2012 at 9:32

Apostolos55

5843 silver badges8 bronze badges

3 Comments

Beta Over a year ago

Thanks Apostilos55! I already modified the code based in Chris answer. But your effort is really appreciated.

Apostolos55 Over a year ago

User697363, don't be in a hurry. The code above doesn't select/activate anything (as Chris proposed) so it is much faster. Plus the assumption I mentioned at top. Just write the sheet names you want to avoid in the SafeShts string and run it.

Beta Over a year ago

Thanks Apostolos55! I wish I could mark both answer correct. But since that's not possible, and I already marked Chris answer correct, I'm sticking with it. But I'm really grateful for your help.

Collectives™ on Stack Overflow

Conditional Inserting using VBA

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related