VBA subtracting ranges

**Eleethal** · 01-20-2009, 11:18 AM

I have two Ranges, which I view as Arrays or Matrices, I want to subtract the two (which do not overlap or union in any way) to get a new third range of the differene. This new range should be the same size, and can be considered a difference of each cell in the range. Note both ranges are already identical in size.

Any help solving this, searched up and down for hours.

Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate

**shg** · 01-20-2009, 12:07 PM

Insert a formula common to all cells, then copy and replace with values.

**Eleethal** · 01-20-2009, 12:08 PM

Can you elaborate a little? I'm trying to do it entirely through VBA without writing to any cells in the worksheets

**shg** · 01-20-2009, 12:18 PM

Please Login or Register  to view this content.

**Eleethal** · 01-20-2009, 12:35 PM

Do I have to supply .formula in the union statement, I don't want to randomize the values I have in both ranges

Edit: been trying stuff this is what I have, but I get an error when I run it

Please Login or Register  to view this content.

The error is "Method 'Union' of object '_Application' failed.

Some vital information, My range size is 1 to 39, 1 to 209

**shg** · 01-20-2009, 01:54 PM

Do I have to supply .formula in the union statement, I don't want to randomize the values I have in both ranges

No, of course not -- it was only there to provide a standalone example.

The error is "Method 'Union' of object '_Application' failed.

EditedRange is undefined.

Put Option Explicit at the top of your module, and declare variables using data types appropriate to usage until it compiles. Also, in this declaration:

Please Login or Register  to view this content.

temp is a variant; if you want to declare it as a range (and you should),

Please Login or Register  to view this content.

**Eleethal** · 01-20-2009, 02:01 PM

Originally Posted by shg

EditedRange is undefined.

Put Option Explicit at the top of your module, and declare variables using data types appropriate to usage until it compiles.

Option Explicit is at the top, and is on. Sorry about missing that declaration, I fixed that up

As for EditedRange, what is that?

**shg** · 01-20-2009, 02:29 PM

Sorry, EditedWeeks

**Eleethal** · 01-20-2009, 02:34 PM

Sorry, I did not post all of the code, EditedWeeks has been defined and filled.

Please Login or Register  to view this content.

Both EditedWeeks and UnEditedWeeks have the same size (width and height) and are populated (I checked with the locals window)

again it crashes at Set temp = Application.Union(EditedWeeks, UnEditedWeeks)

Any help you have is entirely appreciated, and I thank you for the direction thus far...

**shg** · 01-20-2009, 02:39 PM

I can't see all of your code, but I'd reorganize it something like this:

Please Login or Register  to view this content.

**Eleethal** · 01-20-2009, 02:42 PM

Originally Posted by shg

I can't see all of your code, but I'd reorganize it something like this:

Please Login or Register  to view this content.

set rDiff = ' what?
Im confused by that line, and the purpose of rEdit

Also, I added

Please Login or Register  to view this content.

It crashes with error

Object variable or With block variable not set

do i initialize rDiff to nothing..?

Just to clarify, I want a new range the same size (width and height) as the other two ranges, with each cell has the contents of their corresponding difference of the other cells in the other ranges

examples

r1
0 1 2
0 1 2
0 1 2

r2
1 2 3
1 0 1
0 1 0

rDiff
-1 -1 -1
-1 1 1
-0 0 2

Just trying out stuff now, tried using

Please Login or Register  to view this content.

with no luck

**shg** · 01-20-2009, 03:28 PM

You need three ranges (two to subtract, and one to store the results); you've defined one. Where are the other two?

**Eleethal** · 01-20-2009, 03:30 PM

Again I've defined Two, EditedWeeks and UnEditedWeeks. Both have the same size which are width and height.

The third , I desire to be stored in memory, or in a temp worksheet, whichever is possible

**shg** · 01-20-2009, 03:42 PM

Eleethal,

There's no such thing as a 'range in memory'; ranges are on worksheets, and that was the question I was responding to.

If you look at my first example, I defined two source ranges (you've done that) and a destination range (you haven't done that). Then I put the formula in the destination range, not the two source ranges as you have done.

**Eleethal** · 01-20-2009, 03:56 PM

I understand now, I have

Please Login or Register  to view this content.

and it doesn't crash. Thank you very much for the help, but one more issue is that it doesn't produce the right values, it is giving me the wrong results..