formuls
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 |
formuls
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 |
formuls
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 |
formuls
"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 |
formuls
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 |
formuls
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 |
formuls
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 |
formuls
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 |
formuls
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 |
formuls
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 |
formuls
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 |
All times are GMT +1. The time now is 01:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com