Results 1 to 5 of 5

Use VBA to create and insert an indirect relative formula

Threaded View

  1. #1
    Registered User
    Join Date
    05-08-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    2

    Use VBA to create and insert an indirect relative formula

    I imagine it is, but I can't seem to get it quite to work. I'm two days into youtube videos and forum hunting and could use some help!

    I think I'm close with object variables as a range but I can't seem to get the formula to use the formula contained in another cell, much less offset a given amount from that range. I tried it as a string, but then I couldn't use offsets.

    I want to:

    1st) define a variable as a range (a single cell) referenced in formula(not the value) in another cell
    2nd) use that range with offsets to make a FormulaR1C1 formula.

    Sub Choose1()
    
       Dim Fir As Range
             
        Set Fir = ActiveCell
        
               
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=SUM(fir.offset(0,1),fir.offset(10,1),fir.offset(20,1)"
    
    End Sub
    Say Cell A1{activecell} = A3

    And I want the Formula in B1{always activecell.offset(0,1)} to = Sum(B3{always the same row number as the formula in A1, and offset 1 column to the right},B13{always activecell.offset(10,1)},B23{always activecell.offset(20.1)})

    Please help!
    Last edited by Leith Ross; 05-08-2015 at 06:07 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Making the last part of this INDIRECT formula as relative
    By zicitron in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-04-2020, 02:26 PM
  2. How to insert a relative dynamic formula in VBA
    By arskiracer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2013, 12:29 AM
  3. Create macro to insert rows relative to the value
    By ofranco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2012, 04:19 PM
  4. Replies: 5
    Last Post: 02-18-2011, 05:27 AM
  5. Relative Indirect Formula Referencing?
    By Damian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2005, 02:19 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1