You're putting the formula in the range named est ("est" & i). And all that
stuff depends on what's in these cells:

r = tcell.Offset(j, 0)
c = tcell.Offset(j, 1)
nm = tcell.Offset(j, 2)
ycell.Offset(r, c).name = est & nm
Range(ycell.Offset(0, 0), ycell.Offset(17, 24)).name = est & "rng"

ycell.offset(r,c) depends on tcell.offset(j,0) and tcell.offset(j,1) and the
name depends on what's in tcell.offset(j,2).

I'd approach it by adding some watches for each of those variables and addresses
for the cells to see what's going on.



ranswert wrote:[i]

Which other cells?

"Dave Peterson" wrote:
[i]
It incremented for me. But it looks like the results will depend on what is in
those other cells.

ranswert wrote:

I fixed the code with the quotes in right. The problem I have now is the
formula is not incrementing down a 1 row. Here is the code I am using:

Sub anamecells()
Dim xcell As Range
Dim ycell As Range
Dim tcell As Range
Dim i As Integer
Dim j As Integer
Dim est As String
Dim r As Integer
Dim c As Integer
Dim nm As String

Set xcell = Range("b4")
Set tcell = Range("ak3")

For i = 1 To 9
Set ycell = xcell.Offset((i - 1) * 23, 0)
est = "est0" & i
For j = 1 To 19
r = tcell.Offset(j, 0)
c = tcell.Offset(j, 1)
nm = tcell.Offset(j, 2)
ycell.Offset(r, c).name = est & nm
Range(ycell.Offset(0, 0), ycell.Offset(17, 24)).name = est & "rng"
Next
Range(est).FormulaR1C1 = _
"=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i
& "]C[8])"
Range(est & "totalsum") = "=sum(" & est & "totalrng)"

Next

End Sub

"Dave Peterson" wrote:

Just like you put the i outside the double quotes he

Range("est0" & i)

you have to do the same on the other side:

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])"

ranswert wrote:

I need to write a code that inserts a formula into a cell.
The code is:

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!RC[7]&"" ""&'Estimate Costs'!RC[8])"

The row needs to increment 'i' down each time I insert the formula.

I get an error when it is run. I have i as an integer.

What am I doing wrong?
Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson