Need offset range for loop

[Joe Fish]

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 Ogilvy]

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" <joefish534@yahoo.com> wrote in message
news:1129487584.004117.59290@g43g2000cwa.googlegroups.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
>

[Joe Fish]

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

[Tom Ogilvy]

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" <joefish534@yahoo.com> wrote in message
news:1129495265.826903.285330@f14g2000cwb.googlegroups.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
>

[Joe Fish]

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

[Tom Ogilvy]

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" <joefish534@yahoo.com> wrote in message
news:1129503174.372348.60070@g44g2000cwa.googlegroups.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
>