passing a range from another worksheet
 

Hi all,

Apologies for this - this is probably quite a basic question, but I'm afraid
I can't seem to find the answer, and all the bookshops I'd go to for
something to help me out are closed at this time of night. In my defence, I
started working with VBA about four hours ago.

I've got a subroutine (lifted from another thread in this group) which is
adding two ranges. This is great when the ranges are on the same sheet, but
VBA doesn't seem to like attemts to pass ranges from other pages. So when I
try

AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8"))

calling a subroutine

Private Sub AddRanges(RangeOne As Range, RangeTwo As Range)

I get a run-time error 1004 - Method "Range" of object '_Worksheet' failed.
I've tried playing with the quotes and brackets, but haven't had any success.
The Sheet2 reference works fine for a straightforward copy, so is it
something about passing this sort of range as a parameter?

Thanks for any help,
Mike

passing a range from another worksheet
 

Without seeing any code it it hard to tell... but here is a little theory.
The range object is a member of the sheet. So one sheet can have multiple
ranges, but a range can only span one sheet. As a guess the code tries to do
something with both ranges as if they were one big range which they can not
possibly be.
--
HTH...

Jim Thomlinson


"Mike O" wrote:

Hi all,

Apologies for this - this is probably quite a basic question, but I'm afraid
I can't seem to find the answer, and all the bookshops I'd go to for
something to help me out are closed at this time of night. In my defence, I
started working with VBA about four hours ago.

I've got a subroutine (lifted from another thread in this group) which is
adding two ranges. This is great when the ranges are on the same sheet, but
VBA doesn't seem to like attemts to pass ranges from other pages. So when I
try

AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8"))

calling a subroutine

Private Sub AddRanges(RangeOne As Range, RangeTwo As Range)

I get a run-time error 1004 - Method "Range" of object '_Worksheet' failed.
I've tried playing with the quotes and brackets, but haven't had any success.
The Sheet2 reference works fine for a straightforward copy, so is it
something about passing this sort of range as a parameter?

Thanks for any help,
Mike

passing a range from another worksheet
 

I think you are looking for something like this:
Note: the MsgBox is just there to show you how it works...)

Sub AddRanges
'AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8"))

Dim tot as double

tot = worksheetfunction.sum(Sheet1.Range("F6:J10"),Sheet 2.Range("C4:G8"))

msgbox tot

end sub

to make this more generic - you would need to define rng1 as range & rng2 as
range
than
set rng1 =Sheet1.Range("F6:J10")
set rng2 =Sheet2.Range("C4:G8")

tot = worksheetfunction.sum(rng1,rng2)
--
steveB

Remove "AYN" from email to respond
"Mike O" wrote in message
...
Hi all,

Apologies for this - this is probably quite a basic question, but I'm
afraid
I can't seem to find the answer, and all the bookshops I'd go to for
something to help me out are closed at this time of night. In my defence,
I
started working with VBA about four hours ago.

I've got a subroutine (lifted from another thread in this group) which is
adding two ranges. This is great when the ranges are on the same sheet,
but
VBA doesn't seem to like attemts to pass ranges from other pages. So when
I
try

AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8"))

calling a subroutine

Private Sub AddRanges(RangeOne As Range, RangeTwo As Range)

I get a run-time error 1004 - Method "Range" of object '_Worksheet'
failed.
I've tried playing with the quotes and brackets, but haven't had any
success.
The Sheet2 reference works fine for a straightforward copy, so is it
something about passing this sort of range as a parameter?

Thanks for any help,
Mike

passing a range from another worksheet
 

Thanks for that Steve, setting the ranges in range variables did the trick
nicely.

One question though - I know VBA doesn't require the declaration of
variables, in theory. That said, if I do a

set rng = Sheet1.Range("C4:G8")

and attempt to pass this across to a Subroutine which is expecting a Range,
I get a type mismatch. What type is VBA assigning to rng in this case? As you
pointed out, declaring rng as a Range before setting it clears up this
problem, but I'm curious to know what it's being created as, if not as a
Range.

Thanks for the help,
Mike
--
--

passing a range from another worksheet
 

Mike,

rng is being set as a Range object, but only in the module it is defined in.
It will not pass to another routine.

Excel works best when all variables are properly defined.
Improves speed and functioning.

Option Explicit at the top of each module forces Excel to only
accept defined variables. This helps catch typos and other errors
in your code. (it also drives you to distraction when you are first
learning how to use VBE)

When you use "set" you are defining an object. Excel likes this...

But when you use variables between modules you run into minor issues.

When you use Dim variable in a module - it won't pass to another module
When you use Dim variable outside a macro - it should pass to other macros
in the same module.
To pass variables to all macros in all modules - use Public variable outside
any
macro. (I usually do this by creating a module called "MyVariables" and
just
fill it with Public variables and add notes as to what they are.

But be careful - if you create a Public variable, don't dim this variable
inside of
any module - it can cause Excel to crash.

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"Mike O" wrote in message
...
Thanks for that Steve, setting the ranges in range variables did the trick
nicely.

One question though - I know VBA doesn't require the declaration of
variables, in theory. That said, if I do a

set rng = Sheet1.Range("C4:G8")

and attempt to pass this across to a Subroutine which is expecting a
Range,
I get a type mismatch. What type is VBA assigning to rng in this case? As
you
pointed out, declaring rng as a Range before setting it clears up this
problem, but I'm curious to know what it's being created as, if not as a
Range.

Thanks for the help,
Mike
--
--