Sum a range of cells based on relative position

[DaveO]

I have a VBA construct like this one

dim SCell as Range
for each SCell in Selection.Cells
{snip}
next SCell
.... where the selected cells are 1 column wide and several rows deep.

As the For... Next spins through the cells in the selected range, I
need to take some actions if the sum of a range of cells on a given
row is greater than zero. So, if SCell is at cell B15, I need to find
out the sum of cells D15:G15.

What is the code to derive that sum? I'm thinking it's some use of
OFFSET, perhaps, but I'm not sure of the syntax. Any ideas?

Thanks,
Dave O

 

[Jim Cone]

Set sCell = Range("B15")
x = Application.WorksheetFunction.Sum(sCell.Offset(0, 2).Resize(1, 4))
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
("List Files" finds files/folders and creates hyperlinked lists)




"DaveO" <[email protected]>
wrote in message
news:5a57c9e8-7bc4-497a-ae19-fb12a9d7d3db@g26g2000vba.googlegroups.com...

 

[DaveO]

Thanks, Jim, I'll give it a shot. Because the relative position
changes with each loop, I'll need to modify your code to read

Set sCell = Range(sCell.Address)

.... but that's exactly what I was looking for. Thanks!

 

[Jim Cone]

My example was misleading in that the Set code line was only there is indicate the reference cell.
If you are looping thru each cell in the selection, then sCell is automatically "Set" by Excel.
You don't need to and shouldn't set a reference to it during the loop.
On the other hand, the Offset and Resize parameters can be changed to reference different cell
locations.

Also, it is good practice to specify the parent sheet for a range.
Instead of "Range("B17") use Worksheets("Sludge").Range("B17")
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel is useful)



"DaveO" <[email protected]>
wrote in message
news:8cdd5644-89ff-41a1-ab36-83bbf6e51fe3@t35g2000yqj.googlegroups.com...