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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Thanks Bob I will try to figure out how to get my code to run from the bottom as that makes the most sence. Adam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Adam,
For x - 1000 to 1 step -1 Next steve "Adam Ochs" wrote in message ... Thanks Bob I will try to figure out how to get my code to run from the bottom as that makes the most sence. Adam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Thank you Steve
Where the heck do I put that in my code? I am trying now but so far no luck. Adam -----Original Message----- Adam, For x - 1000 to 1 step -1 Next steve "Adam Ochs" wrote in message ... Thanks Bob I will try to figure out how to get my code to run from the bottom as that makes the most sence. Adam . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Adam,
Sorry about that... Also I had a typo, used x-1000 instead of x=1000 For x = 1000 to 1 step -1 your code here Next note that 1000 is my number. You can replace it with your own or before the For ..... Next loop lrow finds the last entry in column A, change to suit your needs. Dim lrow as long lrow = Cells(Rows.Count, "A").End(xlUp).Row For x = lrow to 1 step -1 steve "Adam Ochs" wrote in message ... Thank you Steve Where the heck do I put that in my code? I am trying now but so far no luck. Adam -----Original Message----- Adam, For x - 1000 to 1 step -1 Next steve "Adam Ochs" wrote in message ... Thanks Bob I will try to figure out how to get my code to run from the bottom as that makes the most sence. Adam . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro skipping even rows
Adam,
Chickened out of recursion eh<VBG? In your code, I would suggest that you change Set ColumnB = Range("b2", Range("b" & Rows.Count).End(xlUp).Address) For Each i In ColumnB .... Next i to cRows = Range("b2", Range("b" & Rows.Count).End(xlUp).row ' this gets the row number of the last line ' so now loop from that row up For i = cRows To 1 Step -1 .... Next i Others changes would be needed as you use i as aon object, and use it as such later on, whereas I am suggesting using it as a numeric counter, so you would need to get that object some other way. As an aside, why all this palaver Dim overwrite As Variant overwrite = "" i = newval i.Offset(0, 3) = overwrite Why not just newval.Offset(0,3) = "" There seems to be an awful lot of unnecessary selecting going on, which seems to be easy to remove. For instance i.Offset(0, -1).Select 'copy part number 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 could probably be written as i.Offset(0, -1).Cop y 'copy part number For Counta = 1 To i.Offset(0, 3).Value with Sheets("CRFS_Invintory_Table"). If .Range("B1").Offset(1, 0).Value = "" Then .Range("B1").Offset(1, 0).Paste Else .Range("B1").End(xlDown).Offset(1, 0).Paste End If Next Counta By the way, indent your code, it makes life easier. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Adam Ochs" wrote in message ... Thank you Steve Where the heck do I put that in my code? I am trying now but so far no luck. Adam -----Original Message----- Adam, For x - 1000 to 1 step -1 Next steve "Adam Ochs" wrote in message ... Thanks Bob I will try to figure out how to get my code to run from the bottom as that makes the most sence. Adam . |
Reply |
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 |