Hi,
I have a sub that works fine until I try to loop it. It analyzes a
single cell and either copies the whole row to another sheet or skips
over it, based on its value. Here's the code:

Sub DefineBundles()=
Sheets("Spare Scroller Cables").Select
Range("A2").Select
Sheets("Scroller Info").Select
Range("F2").Select
Do Until ActiveCell = ""
If (ActiveCell.Value) = 50 Then Application.Run "CreateSpare"
Loop
End Sub

Sub CreateSpare()
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Spare Scroller Cables").Select
ActiveSheet.Paste
Selection.Offset(1, 0).Select
Sheets("Scroller Info").Select
Selection.Offset(1, 5).Select
' This is trying to counter-act the row/cell selection issue. It
didn't work.
End Sub

It seems like the problem is that when you return to the original
sheet, Excel doesn't like going back to looking at a cell when it has a
row selected. The offset- obviously- doesn't behave like the arrow
buttons would. I guess you could get around it by reselecting F2 and
doing a compounding offset, but there must be a less clunky way of
doing it.

Any advice is appreciated. Thanks,
Joe

Sub DefineBundles()
Dim cell as Range, rng as Range
Dim j as Long
With Sheets("Spare Scroller Cables")
set rng = .Range(.Range("A2"),Range("A2").End(xldown))
End With
j = 0
for each cell in rng
If Cell.Value = 50 Then
CreateSpare cell, j
j = j + 1
endif
Next
End Sub

Sub CreateSpare(cell1 as Range, Offst as Long)
set DestRange = Worksheets("Scroller Info").Range("F2")
cell1.EntireRow.Copy Destination:=DestRange.Offset(Offst,-5)
End Sub

--
Regards,
Tom Ogilvy


"Joe Fish" wrote in message
ups.com...
Hi,
I have a sub that works fine until I try to loop it. It analyzes a
single cell and either copies the whole row to another sheet or skips
over it, based on its value. Here's the code:

Sub DefineBundles()=
Sheets("Spare Scroller Cables").Select
Range("A2").Select
Sheets("Scroller Info").Select
Range("F2").Select
Do Until ActiveCell = ""
If (ActiveCell.Value) = 50 Then Application.Run "CreateSpare"
Loop
End Sub

Sub CreateSpare()
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Spare Scroller Cables").Select
ActiveSheet.Paste
Selection.Offset(1, 0).Select
Sheets("Scroller Info").Select
Selection.Offset(1, 5).Select
' This is trying to counter-act the row/cell selection issue. It
didn't work.
End Sub

It seems like the problem is that when you return to the original
sheet, Excel doesn't like going back to looking at a cell when it has a
row selected. The offset- obviously- doesn't behave like the arrow
buttons would. I guess you could get around it by reselecting F2 and
doing a compounding offset, but there must be a less clunky way of
doing it.

Any advice is appreciated. Thanks,
Joe

Tom,
This is what I got:
Wrong number of arguments or invalid property assignment.

I would try to debug it, but I am new at this and frankly didn't
understand much of what you did.

Thanks, though.
Joe

I omitted a period in this statement:
Set rng = .Range(.Range("A2"), Range("A2").End(xlDown))

I changed it to

Set rng = .Range(.Range("A2"), .Range("A2").End(xlDown))

After fixing that, it ran fine for me and did exactly what **I** expected.

I also added a declaration for DestRange in CreateSpare in case you have
option explicit declared.

Sub DefineBundles()
Dim cell As Range, rng As Range
Dim j As Long
With Sheets("Spare Scroller Cables")
Set rng = .Range(.Range("A2"), .Range("A2").End(xlDown))
End With
j = 0
For Each cell In rng
If cell.Value = 50 Then
CreateSpare cell, j
j = j + 1
End If
Next
End Sub

Sub CreateSpare(cell1 As Range, Offst As Long)
Dim DestRange As Range
Set DestRange = Worksheets("Scroller Info").Range("F2")
cell1.EntireRow.Copy Destination:=DestRange.Offset(Offst, -5)
End Sub

--
Regards,
Tom Ogilvy


"Joe Fish" wrote in message
oups.com...
Tom,
This is what I got:
Wrong number of arguments or invalid property assignment.

I would try to debug it, but I am new at this and frankly didn't
understand much of what you did.

Thanks, though.
Joe

I got the same message, and it highlighted (highlit?) the "CreateSpare"
before "cell, j"

I'm probably doing something wrong.

Thanks for the help,
Joe

Did you copy in my CreateSpare routine and remove yours? The error says
you probaby didn't. My CreateSpare requires two arguments to be passed to
it. Your CreateSpare doesn't.

As I said, it runs for me - no errors, performs as expected.

--
Regards,
Tom Ogilvy




"Joe Fish" wrote in message
ups.com...
I got the same message, and it highlighted (highlit?) the "CreateSpare"
before "cell, j"

I'm probably doing something wrong.

Thanks for the help,
Joe