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