+ Reply to Thread
Results 1 to 2 of 2

'Tell' excel not to auto increment cell reference?

Hybrid View

Foboman 'Tell' excel not to auto... 01-08-2008, 10:54 AM
mikerickson Range("D1:D20").FormulaR1C1 =... 01-08-2008, 11:02 AM
  1. #1
    Registered User
    Join Date
    12-20-2007
    Posts
    10

    'Tell' excel not to auto increment cell reference?

    I have a problem that im sure theres a simple solution to.
    I have a range of cells (D1:D20) that get a formula entered into through VBA depending on the value of another cell (A1). The forumla that gets inputted looks like this:

    =myfunction(A1)
    Problem is when my code determines D1:d20 need this formula entered into them, it puts 'myfunction(a1) in D1, my function(A2) in D2, myfunction(a3) in D3, etc, etc). Excel automatically keeps on incrementing the cell # im referencing. This isnt what I want. I always need A1 to be the cell referenced.
    Is there a way I can tell excel not to do this? Keep in mind I am not entering the formula in manually, but do so through a snippet of code like this:

    If Target.Address = "$A$1" Then
    If Target.Value <> "Stuff" Then
    Range("D1:D20").Formula = "=myfunction(A1)"

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Range("D1:D20").FormulaR1C1 = "=myfunction(R1C1)"
    or
    Range("D1:D20").Formula = "=myfunction($A$1)"
    Excel uses the term "relative referencing" to describe what you call "auto-incriment".
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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