Results 1 to 5 of 5

Problem with FormulaR1C1 resulting in absolute reference

This is a discussion on Problem with FormulaR1C1 resulting in absolute reference within the Excel Questions forums, part of the Question Forums category; As You can see in the following VB code I will have a problem with the cell references made by ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    72

    Default Problem with FormulaR1C1 resulting in absolute reference

    As You can see in the following VB code I will have a problem with the cell references made by the ForumulaR1C1 resulting in an absolute reference. The problem is that I want to drag the reference down in the cells below, and I want the column to be absolute but not the row.
    Code:
    ActiveCell.FormulaR1C1 = "='EKSP-REPORT-SHARE'!R" & tempRow & "C" & monthColumn
                Selection.AutoFill Destination:=Range(Cells(rangeStart, companyColumn), Cells(rangeEnd, companyColumn)), Type:=xlFillDefault
    The formula makes af reference to another sheet and it looks like this ex:
    Code:
    ='EKSP-REPORT-SHARE'!$L$5
    And I guess what I want is, that the reference looks like this instead:
    Code:
    ='EKSP-REPORT-SHARE'!$L5
    Can I make FormulaR1C1 write this instead or do I have to change it afterwards?

    Jon

  2. #2
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: Problem with FormulaR1C1 resulting in absolute reference

    You are writing the formula using absolute reference. If you want relative reference, you'll have to write the formula that way:

    Code:
    temprow = temprow - activecell.row
    monthColumn = monthColumn - ActiveCell.Column
    ActiveCell.FormulaR1C1 = "='EKSP-REPORT-SHARE'!R[" & tempRow & "]C[" & monthColumn & "]"
    You could put it into another set of temporary variables if you need to keep the original temps. Try this and see if it works. You might have to debug. I haven't tested this at all

    Goblin

  3. #3
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: Problem with FormulaR1C1 resulting in absolute reference

    Oops, just read you post again when I posted and saw that you wanted the column fixed as absolute. My modification made both column and row relative.

    You'll figure it out

    Goblin

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Posts
    72

    Default Re: Problem with FormulaR1C1 resulting in absolute reference

    Hi, I'll just check it out.

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    72

    Default Re: Problem with FormulaR1C1 resulting in absolute reference

    Hi, it worked. Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com