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

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!R[i]C[7]&"" ""&'Estimate Costs'!R[i]C[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

What is "est0"? How is "i" defined and how does it incriment?


--

"ranswert" wrote in message
...[i]
I need to write a code that inserts a formula into a cell.
The code is:

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!R[i]C[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

Remove the space in the sheet name, or replace it by an underscore. This just makes it simpler.

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

But I don't see what you're trying to achieve: the intersection between two rows? There isn't any.

Tell us what your goal is, so we may be able to help

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"ranswert" wrote in message ...
|I need to write a code that inserts a formula into a cell.
| The code is:
|
| Range("est0" & i).FormulaR1C1 = _
| "=('Estimate Costs'!R[i]C[7]&"" ""&'Estimate Costs'!R[i]C[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

"est0" is a part of the defined name for a cell. ('est01,est02...etc.). "i"
is defined as 'dim i as integer' and I am using in it a 'for i = 1 to 62'
code.

"PCLIVE" wrote:
[i]
What is "est0"? How is "i" defined and how does it incriment?


--

"ranswert" wrote in message
...[i]
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

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]

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

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!R[i]C[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

Hi

est0 set to column B because I don't know how you set that

Private Sub FindMemoRow()
est0 = "B"
For i = 1 To 10
Range(est0 & i).FormulaR1C1 = _
"='Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8]"
Next
End Sub

Mike

"ranswert" wrote:
[i]
I need to write a code that inserts a formula into a cell.
The code is:

Range("est0" & i).FormulaR1C1 = _
"=('Estimate Costs'!R[i]C[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

Thanks
I had a problem getting the quotes in the right place.
It works now.

"Dave Peterson" wrote:
[i]
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]

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

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:
[i]
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]

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

It incremented for me. But it looks like the results will depend on what is in
those other cells.

ranswert wrote:[i]

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:
[i]
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

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]

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

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