Reference Cells relative to activecell in a range

[Omar Lujan]

Hello,

I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudocode:

If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......

*****end pseudo

I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....

Here is my code so far:

Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select

'Do for all cells in the row
Do

ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If


Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True

******End Code

Range doesn't like activecell.offset. Does anyone know how to do this?

Thanks in advance for you time and assistance!!

-Omar

 

[DonkeyOte]

not sure I follow in terms of your little snippet...

MAX is traditionally seen like this:

=MAX(A1:B2)

but it can also be written like this:

=MAX(A1,A2,B1,B2)

or even

=MAX(A1:A2,B1:B2)

all three would return the same answer...

in the code provided you're obviously using the multiple arguments method where argument value 1 is your ideal scenario value (-2) and argument value 2 is the most rows/columns you could possibly move given your starting position.

I hope that helps.

Have a look at XL help in native XL on MAX for more examples.