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

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

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

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


.

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


.

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


.