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