problem with offset -how to re-write line?
 

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

problem with offset -how to re-write line?
 

Various ways -

For Each cll In sht.UsedRange.Columns(2).Cells
if cll.row = 2 then

In theory the UR might start in row 2 or lower and I don't suppose you'd
want to process the first row, even if it doesn't fail

for each sht in thisworkbook.worksheets
bFlag = True
For Each cll In sht.UsedRange.Columns(2).Cells
If bFlag then
bFlag = false
else
' code to check and insert etc
End if

Another thing, do you want to process sheets in which the UR is only one
column, eg an empty sheet. If not, skip the sheet if
sht.UsedRange.Columns.Count < 2

Regards,
Peter T


"PBcorn" wrote in message
...
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