"passing" variable trough routine
 

hi all.
here's my problem.
I've to track down 2 ranges (in one routine), then in the other routine
I use the 2 ranges to paste values I find with the current routine.
my code is wrong, and it's the follow:

(suppose you've numbers in the cells (A1:A4), and in the cell B1)

=============================

Sub ref ()
Range("A1").Copy
Call FindRange(myrangeOrz)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Call FindRange(myrangeVert)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

----------------------------------------------------------------
Sub Find Range(ByRef myrangeOrz as Variant, ByRef myrangeVert as Variant)
Range("A1").End(xlToRight).Offset(0,1).Select
Set myrangeOrz = activecell
Range("A1").End(xlDown).Offset(0,1).Select
Set myrangeVert = activecell

End Sub

------------------------------------------------------------------------------------

So, if I work with only myrangeOrz, code's right.
But with the second range, my code's wrong: I think my problem's
about -- Set myrangeOrz = activecell.
How can I assign the right range to my variables and use them in the other
routine ?
helps very appreciated !

"passing" variable trough routine
 

Sub ref()
Range("A1").Copy
Range("A1").End(xltoRight)(1,2).PasteSpecial xlValues
Range("A1").End(xldown)(2).PasteSpecial xlValues
End sub

would paste in C1 and A5.

--
Regards,
Tom Ogilvy


"asburypark98" wrote in message
...
hi all.
here's my problem.
I've to track down 2 ranges (in one routine), then in the other routine
I use the 2 ranges to paste values I find with the current routine.
my code is wrong, and it's the follow:

(suppose you've numbers in the cells (A1:A4), and in the cell B1)

=============================

Sub ref ()
Range("A1").Copy
Call FindRange(myrangeOrz)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Call FindRange(myrangeVert)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

----------------------------------------------------------------
Sub Find Range(ByRef myrangeOrz as Variant, ByRef myrangeVert as Variant)
Range("A1").End(xlToRight).Offset(0,1).Select
Set myrangeOrz = activecell
Range("A1").End(xlDown).Offset(0,1).Select
Set myrangeVert = activecell

End Sub

--------------------------------------------------------------------------
----------

So, if I work with only myrangeOrz, code's right.
But with the second range, my code's wrong: I think my problem's
about -- Set myrangeOrz = activecell.
How can I assign the right range to my variables and use them in the other
routine ?
helps very appreciated !

"passing" variable trough routine
 

"Tom Ogilvy" wrote:

Sub ref()
Range("A1").Copy
Range("A1").End(xltoRight)(1,2).PasteSpecial xlValues
Range("A1").End(xldown)(2).PasteSpecial xlValues
End sub

would paste in C1 and A5.

--
Regards,
Tom Ogilvy


"asburypark98" wrote in message
...
hi all.
here's my problem.
I've to track down 2 ranges (in one routine), then in the other routine
I use the 2 ranges to paste values I find with the current routine.
my code is wrong, and it's the follow:

(suppose you've numbers in the cells (A1:A4), and in the cell B1)

=============================

Sub ref ()
Range("A1").Copy
Call FindRange(myrangeOrz)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Call FindRange(myrangeVert)
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

----------------------------------------------------------------
Sub Find Range(ByRef myrangeOrz as Variant, ByRef myrangeVert as Variant)
Range("A1").End(xlToRight).Offset(0,1).Select
Set myrangeOrz = activecell
Range("A1").End(xlDown).Offset(0,1).Select
Set myrangeVert = activecell

End Sub

--------------------------------------------------------------------------
----------

So, if I work with only myrangeOrz, code's right.
But with the second range, my code's wrong: I think my problem's
about -- Set myrangeOrz = activecell.
How can I assign the right range to my variables and use them in the other
routine ?
helps very appreciated !

"passing" variable trough routine
 

hi Tom,
thank you for your reply.
It works; but I write this code because in my work
I want to bring both the two ranges in the other routine.
If I use the "select" command, routine remember only the last
range and not the previous.
So how can I bring both ranges from the Sub FindRange() to the
Sub ref() routine ?

regards,
ap98


"Tom Ogilvy" wrote:

Sub ref()
Range("A1").Copy
Range("A1").End(xltoRight)(1,2).PasteSpecial xlValues
Range("A1").End(xldown)(2).PasteSpecial xlValues
End sub

would paste in C1 and A5.

--
Regards,
Tom Ogilvy

"passing" variable trough routine
 

Sub ref ()
Range("A1").Copy
Call FindRange(myrangeOrz, myrangeVert)
MyrangeOrz.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyRangeVert.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

Sub Find Range(ByRef myrangeOrz as Variant, ByRef myrangeVert as Variant)
Set myrangeOrz = Range("A1").End(xlToRight).Offset(0,1)
Set myrangeVert = Range("A1").End(xlDown).Offset(0,1)
End Sub

--
Regards,
Tom Ogilvy



"asburypark98" wrote in message
...
hi Tom,
thank you for your reply.
It works; but I write this code because in my work
I want to bring both the two ranges in the other routine.
If I use the "select" command, routine remember only the last
range and not the previous.
So how can I bring both ranges from the Sub FindRange() to the
Sub ref() routine ?

regards,
ap98


"Tom Ogilvy" wrote:

Sub ref()
Range("A1").Copy
Range("A1").End(xltoRight)(1,2).PasteSpecial xlValues
Range("A1").End(xldown)(2).PasteSpecial xlValues
End sub

would paste in C1 and A5.

--
Regards,
Tom Ogilvy

"passing" variable trough routine
 

Thanx a lot Tom!
code works now !


"Tom Ogilvy" wrote:

Sub ref ()
Range("A1").Copy
Call FindRange(myrangeOrz, myrangeVert)
MyrangeOrz.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyRangeVert.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

Sub Find Range(ByRef myrangeOrz as Variant, ByRef myrangeVert as Variant)
Set myrangeOrz = Range("A1").End(xlToRight).Offset(0,1)
Set myrangeVert = Range("A1").End(xlDown).Offset(0,1)
End Sub

--
Regards,
Tom Ogilvy