For Each Next help

  • Thread starter Thread starter troy_lee
  • Start date Start date
T

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.
 
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.
Click to expand...

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

Try omitting the Resize

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

--ron
 
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).
 
If you want a running total of b2 to the last cell, try this
Sub ddd()
Range("TotalsCells").Formula = "=$b$2:b2"
End Sub
 
This is the answer for anyone interested.

Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
 
Don Guillett said:
If ?? you like r1c1 style
Click to expand...

Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
require much greater complexity when not working with hardcoded
references.
[email protected] said:
This is the answer for anyone interested.

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

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$2:D4). If TotalCells were X99, the formula would become =SUM(X
$2:X98). R1C1 reduces errors once you get used to it.
 
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)
Harlan Grove said:
Don Guillett said:
If ?? you like r1c1 style
Click to expand...

Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
require much greater complexity when not working with hardcoded
references.
[email protected] said:
This is the answer for anyone interested.

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

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$2:D4). If TotalCells were X99, the formula would become =SUM(X
$2:X98). R1C1 reduces errors once you get used to it.
Click to expand...
 
Back
Top