For Each Next help

[troy_lee]

Here is my code.

Dim C As Range
For Each C In Range("TotalsCells").Resize(1, 0) -----IT FAILS
AT THIS LINE
Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)"
Next C

I have a couple of questions.

Is my variable declaration correct? I want to inspect each cell in the
range.

Is my Resize syntax correct for inspecting each cell?

Is my Sum syntax correct?

Thanks in advance for your help.

 

[Ron Rosenfeld]

Here is my code.

Dim C As Range
For Each C In Range("TotalsCells").Resize(1, 0) -----IT FAILS
AT THIS LINE
Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)"
Next C

I have a couple of questions.

Is my variable declaration correct? I want to inspect each cell in the
range.

Is my Resize syntax correct for inspecting each cell?

Is my Sum syntax correct?

Thanks in advance for your help.

I assume "TotalsCells" is a range name defined in your worksheet.

Try omitting the Resize

for each c in range("TotalsCells")
...

--ron

 

[troy_lee]

Yes, it is a declared range.

I tried this code without the resize. It locks up the Excel and forces
me to close it through the Task Manager dialog box. I'm guessing
because the loop is faulty.

With the resize code I get an application or object defined error
(rather ambiguous, at best).

 

[Don Guillett]

If you want a running total of b2 to the last cell, try this
Sub ddd()
Range("TotalsCells").Formula = "=$b$2:b2"
End Sub

 

[troy_lee]

This is the answer for anyone interested.

Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

 

[Don Guillett]

If ?? you like r1c1 style

 

[Harlan Grove]

If ?? you like r1c1 style

Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
require much greater complexity when not working with hardcoded
references.

This is the answer for anyone interested.

Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

You don't need to know where TotalCells is, but the formula will
always sum from row 2 to the row just above TotalCells in the same
column as TotalCells. If TotalCells were D5, the formula would become
=SUM(D$24). If TotalCells were X99, the formula would become =SUM(X
$2:X98). R1C1 reduces errors once you get used to it.

 

[Don Guillett]

Harlan, I didn't say there was anything wrong with it, if that is what is
preferred. I just don't like Jack Daniels when there is Wild Turkey
available.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

If ?? you like r1c1 style

Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
require much greater complexity when not working with hardcoded
references.

This is the answer for anyone interested.

Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

You don't need to know where TotalCells is, but the formula will
always sum from row 2 to the row just above TotalCells in the same
column as TotalCells. If TotalCells were D5, the formula would become
=SUM(D$24). If TotalCells were X99, the formula would become =SUM(X
$2:X98). R1C1 reduces errors once you get used to it.