| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 ! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 ! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 ! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| "Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| reading variable "from" and "to" ZIP codes | Excel Programming | |||
| VBA "Save As Text" Routine? | Excel Programming | |||
Linear Mode
