Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
I am having problems inserting contents of two cells in to one cell. I am
using the following code to name cells in a range and inserting a formula into another cell. 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 = 2 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 The formula I am trying to insert into a cell needs to increment down one row. the code I am using is: Range(est).FormulaR1C1 = _ "=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])" Instead of icrementing down one row it is incrementing down in multiples of 23. I assume because of where I am setting ycell. How do I get this formula to imcrement down only 1 row at a time? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Set ycell = xcell.Offset((i - 1) * 23, 0)
You're right, since you have the above line inside the For...Next loop, it will increment an additional 23 rows every time i increments by 1. You probably want to use ycell = xcell. Offset((i-2) + 23, 0). That would put ycell 23 rows below xcell, but since you have 9 iterations, that means that ycell would contain the cell value of the last iteration. So that does not make sense either. What do you want ycell to equal? It would be easier to assist you if you could clearly state what your objective is. The For..Next loop does only increment the value of i by 1 for each iteration. But when you multiply i by any number in a Cells(i * #, #) configuration, it will set that cell at the row that that multiple equals. I don't know if any of this helps, but it will give you something to think about. "ranswert" wrote: I am having problems inserting contents of two cells in to one cell. I am using the following code to name cells in a range and inserting a formula into another cell. 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 = 2 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 The formula I am trying to insert into a cell needs to increment down one row. the code I am using is: Range(est).FormulaR1C1 = _ "=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])" Instead of icrementing down one row it is incrementing down in multiples of 23. I assume because of where I am setting ycell. How do I get this formula to imcrement down only 1 row at a time? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
What I am trying to do is set up a worksheet that has 62 ranges of
information in it. Inside each range I am naming cells to be able to refer to them with different procedures. The cell that I am trying to put the formula in is at the top left of each range. the top of each range is 23 rows apart. The formula is refering back to a different worksheet with a list of items that i am using to identify each of the 62 ranges. This code is just to input all the info and name all the cells so that I don't have to do it manually 62 times where I might make a mistake and is time consuming. Is there another way to input the formula in increments of 23 row, but only increments one row for the cell it is copying? Thanks for your help "JLGWhiz" wrote: Set ycell = xcell.Offset((i - 1) * 23, 0) You're right, since you have the above line inside the For...Next loop, it will increment an additional 23 rows every time i increments by 1. You probably want to use ycell = xcell. Offset((i-2) + 23, 0). That would put ycell 23 rows below xcell, but since you have 9 iterations, that means that ycell would contain the cell value of the last iteration. So that does not make sense either. What do you want ycell to equal? It would be easier to assist you if you could clearly state what your objective is. The For..Next loop does only increment the value of i by 1 for each iteration. But when you multiply i by any number in a Cells(i * #, #) configuration, it will set that cell at the row that that multiple equals. I don't know if any of this helps, but it will give you something to think about. "ranswert" wrote: I am having problems inserting contents of two cells in to one cell. I am using the following code to name cells in a range and inserting a formula into another cell. 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 = 2 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 The formula I am trying to insert into a cell needs to increment down one row. the code I am using is: Range(est).FormulaR1C1 = _ "=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])" Instead of icrementing down one row it is incrementing down in multiples of 23. I assume because of where I am setting ycell. How do I get this formula to imcrement down only 1 row at a time? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Since I can't see the values in all of the cells that are called out in the
macro, it is difficult to tell exactly what it should be doing. But, it is clear that the formula is to be entered into Range(est). The variable est is equated to "est0" & i which only increments a value or one per iteration. This is where I ran into a problem trying to analyze why it moved 23 rows, because I can't see where that value is assigned as a range name. When I first looked at it, I thought the line: ycell.Offset(r, c).name = est & nm was the tie in, but that actually appends additional characters to the variable est. That leaves a logical disconnect in my mind. So, as I see it now, you should not be getting the formula entered anywhere. Again, I can't see the data in the cells for the values of r, c and nm so maybe they provide the parts that I can't otherwise figure out from your code. So my summary analysis is, that the variable est is only incremented by a value of 1 and if it entered somewhere as a name, that is where the formula will be entered for that iteration of that loop. "ranswert" wrote: What I am trying to do is set up a worksheet that has 62 ranges of information in it. Inside each range I am naming cells to be able to refer to them with different procedures. The cell that I am trying to put the formula in is at the top left of each range. the top of each range is 23 rows apart. The formula is refering back to a different worksheet with a list of items that i am using to identify each of the 62 ranges. This code is just to input all the info and name all the cells so that I don't have to do it manually 62 times where I might make a mistake and is time consuming. Is there another way to input the formula in increments of 23 row, but only increments one row for the cell it is copying? Thanks for your help "JLGWhiz" wrote: Set ycell = xcell.Offset((i - 1) * 23, 0) You're right, since you have the above line inside the For...Next loop, it will increment an additional 23 rows every time i increments by 1. You probably want to use ycell = xcell. Offset((i-2) + 23, 0). That would put ycell 23 rows below xcell, but since you have 9 iterations, that means that ycell would contain the cell value of the last iteration. So that does not make sense either. What do you want ycell to equal? It would be easier to assist you if you could clearly state what your objective is. The For..Next loop does only increment the value of i by 1 for each iteration. But when you multiply i by any number in a Cells(i * #, #) configuration, it will set that cell at the row that that multiple equals. I don't know if any of this helps, but it will give you something to think about. "ranswert" wrote: I am having problems inserting contents of two cells in to one cell. I am using the following code to name cells in a range and inserting a formula into another cell. 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 = 2 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 The formula I am trying to insert into a cell needs to increment down one row. the code I am using is: Range(est).FormulaR1C1 = _ "=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])" Instead of icrementing down one row it is incrementing down in multiples of 23. I assume because of where I am setting ycell. How do I get this formula to imcrement down only 1 row at a time? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Thanks for your help
I will try something different "JLGWhiz" wrote: Since I can't see the values in all of the cells that are called out in the macro, it is difficult to tell exactly what it should be doing. But, it is clear that the formula is to be entered into Range(est). The variable est is equated to "est0" & i which only increments a value or one per iteration. This is where I ran into a problem trying to analyze why it moved 23 rows, because I can't see where that value is assigned as a range name. When I first looked at it, I thought the line: ycell.Offset(r, c).name = est & nm was the tie in, but that actually appends additional characters to the variable est. That leaves a logical disconnect in my mind. So, as I see it now, you should not be getting the formula entered anywhere. Again, I can't see the data in the cells for the values of r, c and nm so maybe they provide the parts that I can't otherwise figure out from your code. So my summary analysis is, that the variable est is only incremented by a value of 1 and if it entered somewhere as a name, that is where the formula will be entered for that iteration of that loop. "ranswert" wrote: What I am trying to do is set up a worksheet that has 62 ranges of information in it. Inside each range I am naming cells to be able to refer to them with different procedures. The cell that I am trying to put the formula in is at the top left of each range. the top of each range is 23 rows apart. The formula is refering back to a different worksheet with a list of items that i am using to identify each of the 62 ranges. This code is just to input all the info and name all the cells so that I don't have to do it manually 62 times where I might make a mistake and is time consuming. Is there another way to input the formula in increments of 23 row, but only increments one row for the cell it is copying? Thanks for your help "JLGWhiz" wrote: Set ycell = xcell.Offset((i - 1) * 23, 0) You're right, since you have the above line inside the For...Next loop, it will increment an additional 23 rows every time i increments by 1. You probably want to use ycell = xcell. Offset((i-2) + 23, 0). That would put ycell 23 rows below xcell, but since you have 9 iterations, that means that ycell would contain the cell value of the last iteration. So that does not make sense either. What do you want ycell to equal? It would be easier to assist you if you could clearly state what your objective is. The For..Next loop does only increment the value of i by 1 for each iteration. But when you multiply i by any number in a Cells(i * #, #) configuration, it will set that cell at the row that that multiple equals. I don't know if any of this helps, but it will give you something to think about. "ranswert" wrote: I am having problems inserting contents of two cells in to one cell. I am using the following code to name cells in a range and inserting a formula into another cell. 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 = 2 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 The formula I am trying to insert into a cell needs to increment down one row. the code I am using is: Range(est).FormulaR1C1 = _ "=('Estimate Costs'!R[" & i & "]C[7]&"" ""&'Estimate Costs'!R[" & i & "]C[8])" Instead of icrementing down one row it is incrementing down in multiples of 23. I assume because of where I am setting ycell. How do I get this formula to imcrement down only 1 row at a time? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |