Formulas

[ranswert]

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

 

[JLGWhiz]

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]

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

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.

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

 

[JLGWhiz]

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.

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

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.

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

 

[ranswert]

Thanks for your help
I will try something different

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.

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

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.


:

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