Consulting

Results 1 to 5 of 5

Thread: Solved: Offset To Add Formula

  1. #1

    Solved: Offset To Add Formula

    Hi,

    I'm trying to add a forumla every three cell in the range of R4:R414 with offset.

    not sure how to insert the formula every time

    [VBA]Range("R4").FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
    ActiveCell.Offset(3, 0).Select
    [/VBA]


    Thank you for your time

    Nurofen

  2. #2
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    273
    Location
    to use your same setup the folowing code will work

    [vba]Sub testing()
    Dim a As Long
    Range("R4").Select
    Range("R4").FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
    For a = 3 To 410 Step 3
    ActiveCell.Offset(a, 0).FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
    Next
    End Sub
    [/vba]

    i would do something like this

    [VBA]Sub testing()
    Dim a As Long
    For a = 4 To 414 Step 3
    Range("R" & a).FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
    Next
    End Sub[/VBA]

  3. #3
    Thanks figment,

    works great


    Thank for your time

    Nurofen

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Another way

    [vba]

    Sub testing()
    Dim i As Long
    Dim rng As Range
    Set rng = Range("R4")
    For i = 7 To 414 Step 3
    Set rng = Union(rng, Cells(i, "R"))
    Next i
    rng.FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Thanks Xld


    Thank you for your time

    Nurofen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •