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
    Sub Loop1()
          Dim Rw As Long, Cl As Long
          Range("C2").Select
                   Do
                         ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
                         ActiveCell.Offset(1, 0).Select
                   Loop Until IsEmpty(ActiveCell.Offset(0, -1))
          Range("A22").Select
    End Sub


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

  • Hi,


    Try this:


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


    This will give the result you want

    Code
    Sub test()
        With ActiveSheet
            .[c2].Formula = "=average(a2,b2)"
            .[c2].Resize(.[b2].End(xlDown).Row - 1).FillDown
            Application.Goto .[a22]
        End With
    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
    Sub R_C()
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
        Range("A4").Select
    End Sub



    Code
    Sub ne_w()
    Range("A3").Select
        ActiveCell.Formula = "=SUM(A1:A2)"
        Range("A4").Select
        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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!