Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Adam,
I think the problem is caused as you delete rows. AT this point the row moves on, so when you then do a Next, it moves on again, skipping a row. Try one of two things - modifying your code to work from the bottom up (this is what I would do) - using recursive code, by counting any deletions, if there are some, call the sub again, sort of Sub myProc() Dim cDeletes as Long For ... If delete cDeletes = cDeletes + 1 ... other code ... Nextr If CDeletes < 0 Then myproc() End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Adam Ochs" wrote in message ... Hello all I have the following code written and it works great except I have to run it multiple times as it skips over the even rows. Any suggestions? Thanks Adam Sub cpyprtnbr() Sheets("on_order").Select 'select on order sheet Dim ColumnB As Range ' i dont know why Dim i As Range 'i dont know why Set ColumnB = Range("b2", Range("b" & Rows.Count).End (xlUp).Address) 'beats me For Each i In ColumnB 'each i should be each cell column b If i = i.Offset(0, 3) And i.Offset(0, 3) 0 Then 'have all parts been recieved i.Offset(0, -1).Select 'copy part number Dim Counta As Integer For Counta = 1 To i.Offset(0, 3).Value Selection.Copy Sheets("CRFS_Invintory_Table").Select If Range("B1").Offset(1, 0).Value = "" Then Range("B1").Offset(1, 0).Select Else Range("B1").End(xlDown).Offset(1, 0).Select End If ActiveSheet.Paste Next Counta 'paste untill number of pastes equal number recieved Sheets("on_order").Select 'go back to on order i.Select Selection.EntireRow.Select Selection.Delete 'delete on order entry all parts ordered recieved ElseIf i i.Offset(0, 3) And i.Offset(0, 3) 0 Then 'not all parts recieved i.Offset(0, -1).Select Dim Countb As Integer For Countb = 1 To i.Offset(0, 3).Value Selection.Copy Sheets("CRFS_Invintory_Table").Select If Range("B1").Offset(1, 0).Value = "" Then Range("B1").Offset(1, 0).Select Else Range("B1").End(xlDown).Offset(1, 0).Select End If ActiveSheet.Paste Next Countb 'paste untill paste equals number of parts recieved Sheets("on_order").Select 'reselect on order sheet Dim newval As Variant newval = i - i.Offset(0, 3) Dim overwrite As Variant overwrite = "" i = newval i.Offset(0, 3) = overwrite 'set still on order to parts not recieved and make recieved blank End If Next i End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping rows | Excel Worksheet Functions | |||
Skipping Rows with cursor | Excel Discussion (Misc queries) | |||
Copy while skipping Rows | Excel Discussion (Misc queries) | |||
Skipping rows with VBA | Excel Discussion (Misc queries) | |||
Trouble with skipping rows | Excel Worksheet Functions |