Reference Cells relative to activecell in a range

Welcome to the Board

To sum range 2 left and 2 up to 2 up:


However you need to be careful that you don't offset "off" the sheet... ie if the active cell is B2 you have a problem...

Rather than having lots of worksheetfunctions (Max tests etc) you may find it easier to test for values prior to offsetting... eg


I've not tested the remainder of your code but hope the above makes sense and is helpful ?

To add: One thing I have tried is removing the "" for example

If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0

instead of

If Application.WorksheetFunction.Sum(Range( _
"ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2)")) = 0 Then
ActiveCell.Value = 0

Neither works

Thanks again for your help,

-Omar

Hello DonkeyOte - (sweet handle btw!)

Thanks for the welcome and the super fast response!!


Your first analysis was correct; I was offsetting the cells off of the page. Although I feel sheepish, I'm glad my code works. Such is progamming....


I'm not 100% sure if I understand the code in your second suggestion. In my experience Worksheetfunction.Max() is used to find the maximum value in a range. You're code works fine. Will you explain the benefit of doing it that way?

Also, in the syntax of ...max(-2, 1 - activecell.row), What is the "1 - activecell.row" for? I don't understand why you subtract the current row and column from 1.

Thanks again!

Omar

Sure.

The MAX function will return the Max of whatever values appear within the range of values specified... normally as you say this would be a range of values assigned by a user... but in this case we can use it to ensure that you can never offset the page...

let's take B2 as the active cell and try and offset up 2 rows and 2 columns left...
we know this is going to result in an invalid reference if we go up 2 rows from the current row we get to row 0 and if we offset 2 columns to the left we get to column 0 which is also invalid.

However, although we can't move -2,-2 we can move to some extent - ie we can move to A1, right ? ie -1,-1

So we basically setup some parameters that determine what the greatest movement we can do is but with the proviso that we want to limit that movement to at most -2 rows & -2 columns, eg if we're in D4 we don't want to move to A1 we want to move to B2 so we cap the movement.

Let's go back to our first example of B2 being active:

This:

...Max(-2,1-ActiveCell.Row)

equates to Max(-2,-1) -- the -1 being (1-2) where 2 = active row... the 1 in the equation is to represent the first valid row in the worksheet ... this tells us the most rows we could move if uncapped

this will result in a value of -1 as -1 > -2

...Max(-2,1-ActiveCell.Column)

does the same thing as above but looks at the active column number (1 represents column A) -- tells us the most columns we could move if uncapped, so in the case of B2 it will result again in -1 as -1 > -2

If we use the example of D4 as active cell then the values returned would be:

Row:
Max(-2,-3) so -2

Column
Max(-2,-3) so -2 again

We can then use these values in our offset to always ensure we never go "off limits" -- our movement will be capped such that we can only go to as high as row 1 or as far left as column 1 but where possible we'll move as much as possible between the -2,-2 boundaries.

Does that make sense ?

Long winded but probably worth it...

Ok, I think I almost have it. Using E5 as an example;

...Max(-2, -4)

means to move it -2 unless the -4 takes you beyond row 1, right? I'm not sure of the syntax, How do you figure the value returned.


Thanks for your time in explaining. I'm learning something new today.

-Omar

not quite...it says based on E5 being active cell

the -4 means that from E5 I can move up at most 4 rows... if I move up any more than that I'll be off the sheet...as -4 takes me to row 1.

However I don't want to move up more than 2 rows (-2) ... so in this case the MAX(-2,-4) will return -2 as the former is greater than the latter (being negative number) thus the variable I'd use in my offset is set to be -2.

Using the B2 example again you get MAX(-2,-1) where the -1 represents the most rows you can move up given the active cell position...and the result of that equation would be -1 as -1 is greater than -2 (being negative values).

the -4 means that from E5 I can move up at most 4 rows... if I move up any more than that I'll be off the sheet...as -4 takes me to row 1.

Click to expand...

-That's what I meant in my prev post. I got that.


So Max() will return the greater number. Awesome!

oops, clicked post too soon...

I can see how it can be used in this example. What an awesome function!

One last question: In the syntax, what are the arguments?

...max( "# to try first" , "max ") and it returns the greater of the two?

I found this link to explain syntax better

http://www.techonthenet.com/excel/formulas/max.php

DonkeyOte, I owe you a million thanks! Not only solved my problem, but taught me a new way to use an old function to prevent it from happening. Thanks for your patience and time explaining this to me!