incrementing R1C1 Reference using loop

  • Hi.,


    I am trying to create name list for a column. my values are in Column A up to 10 rows, i would like to assign name in B column corresponding rows. For ex: for LenZ in R1C1, the list name in R1C2, should be LenZ ( ie same name). i tried a lot but unable to increment the R1C1 value. Can anyone help on this.

    Code
    1. Sub kre3()
    2. Dim rw As Integer, cl As Integer
    3. rw = 1
    4. cl = 2
    5. If rw = 10 Then ActiveWorkbook.Names.Add Name:=Cells(rw, 1), RefersToR1C1:="=Sheet1!R[" & rw & "]C[" & cl & "]"
    6. rw = rw + 1
    7. End If End Sub

    Files

    • Book1.xlsx

      (8.2 kB, downloaded 66 times, last: )
  • I think you are creating named ranges? There is no loop in the code you are using, I am not exactly sure what you are trying to do but I think it is something like this:


    Code
    1. Sub kre3()
    2. Dim rw As Integer, cl As Integer
    3. rw = 1
    4. cl = 2
    5. For rw = 1 To 10
    6. ActiveWorkbook.Names.Add Name:=Sheet1.Cells(rw, 1).Value, RefersTo:=Sheet1.Cells(rw, 2)
    7. Next rw
    8. End Sub