Problem with FormulaR1C1 resulting in absolute reference

[Jon Jagd]

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.

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:

='EKSP-REPORT-SHARE'!$L$5

And I guess what I want is, that the reference looks like this instead:

='EKSP-REPORT-SHARE'!$L5

Can I make FormulaR1C1 write this instead or do I have to change it afterwards?

Jon

 

[goblin]

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

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

 

[goblin]

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

 

[Jon Jagd]

Hi, I'll just check it out.

 

[Jon Jagd]

Hi, it worked. Thanks!