P
PBcorn
The following very simple code generates a 1004 error (* indicates line).
Help required! Thanks. I think the problem may be due to the fact that he
usedrange starts from row 1, therefore during the AND logical test offset(-1)
refers to a row that does not exist. However I am unsure of the best way to
re-write this. The purpose of the AND test is to ignore rows that have just
been inserted, otherwise the macro inserts multiple rows above cll.
subxy()
dim cll as range
dim sht as worksheet
'for each sheet in workbook
for each sht in thisworkbook.worksheets
'for each cell in column 2 of the usedrange
For Each cll In sht.UsedRange.Columns(2).Cells
' if the value of the cell is "xy", and the cell above it is not 0, insert a
row.
If cll.Value = "xy" And cll.Offset(-1, 0).value <> 0 Then*
cll.EntireRow.Insert
Else: End If
Next cll
next sht
end sub
Help required! Thanks. I think the problem may be due to the fact that he
usedrange starts from row 1, therefore during the AND logical test offset(-1)
refers to a row that does not exist. However I am unsure of the best way to
re-write this. The purpose of the AND test is to ignore rows that have just
been inserted, otherwise the macro inserts multiple rows above cll.
subxy()
dim cll as range
dim sht as worksheet
'for each sheet in workbook
for each sht in thisworkbook.worksheets
'for each cell in column 2 of the usedrange
For Each cll In sht.UsedRange.Columns(2).Cells
' if the value of the cell is "xy", and the cell above it is not 0, insert a
row.
If cll.Value = "xy" And cll.Offset(-1, 0).value <> 0 Then*
cll.EntireRow.Insert
Else: End If
Next cll
next sht
end sub