formuls

[ranswert]

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

 

[PCLIVE]

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

 

[Niek Otten]

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


|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

 

[ranswert]

"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.

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


--

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]

Just like you put the i outside the double quotes here:

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])"

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

 

[Mike H]

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]

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

Just like you put the i outside the double quotes here:

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])"

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

 

[ranswert]

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

Just like you put the i outside the double quotes here:

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])"

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.

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

Just like you put the i outside the double quotes here:

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])"

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

 

[ranswert]

Which other cells?

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

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

Just like you put the i outside the double quotes here:

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]

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

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.

Which other cells?

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

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

:

Just like you put the i outside the double quotes here:

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