VBA from R1C1 to A1

  • Hello all.
    Please, in case it is possible, I need to change this code from the notation R1C1 to A1, I have been trying and I got errors, so thats why I am here

    Code
    1. Sub Loop1()
    2. Dim Rw As Long, Cl As Long
    3. Range("C2").Select
    4. Do
    5. ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    6. ActiveCell.Offset(1, 0).Select
    7. Loop Until IsEmpty(ActiveCell.Offset(0, -1))
    8. Range("A22").Select
    9. End Sub


    work good, but I really would like to see it with A1 notation.
    Thank you for reading this

  • Hi,


    Try this:


    Code
    1. Sub Loop2()
    2. Dim Rw As Long, Cl As Long
    3. Range("C2").Select
    4. Do
    5. ActiveCell.Formula = WorksheetFunction.Average(ActiveCell.Offset(, -1), ActiveCell.Offset(, -2))
    6. ActiveCell.Offset(1, 0).Select
    7. Loop Until IsEmpty(ActiveCell.Offset(0, -1))
    8. Range("A22").Select
    9. End Sub
  • Why use a loop?


    This will give the result you want

    Code
    1. Sub test()
    2. With ActiveSheet
    3. .[c2].Formula = "=average(a2,b2)"
    4. .[c2].Resize(.[b2].End(xlDown).Row - 1).FillDown
    5. Application.Goto .[a22]
    6. End With
    7. End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you Ingo_Ingo.
    Thank you KjBox
    .


    I really appreciate the time you have spent answering this. Let me tell you more or less what I am doing;
    so better show you what I am trying to do:

    Code
    1. Sub R_C()
    2. Range("A3").Select
    3. ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    4. Range("A4").Select
    5. End Sub



    Code
    1. Sub ne_w()
    2. Range("A3").Select
    3. ActiveCell.Formula = "=SUM(A1:A2)"
    4. Range("A4").Select
    5. End Sub


    Here when I change R1C1 Notation for a regular Notation, is a better way for me to understand what's going on, that's why I was trying with the Do loop, but nothing work, except RC.
    And all this is because my real target is to learn how to loop the coefficient trendlines, so for that reazon I start in a very basic way.
    Work around the statement ► WITH.....END WITH◄ for me at lease, is really complex. Reading a problem and decide when to apply "with" Thanks KjBox. I am a new bee.
    Thanks all.