Problem with absolute reference in excel vba 1

[kokon]

Hi I have the below code inside 3 "For" loops. I need to insert in few cells the same formula with absolute references to $J$119 cell.
That code works but it inserts $J$119*100, $J$120*100, $J$121*100 like relative reference. Please help


Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 8).Formula = "=" & Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 5).Address(0, 0) & "/ $J$119*100"

 

[SkipVought]

Really?

I ran your code and the $J$119 remaind ABSOLUTE!

???

For i = 1 To 2: For j = 1 To 2: For k = 1 To 2
Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 8).Formula = _
    "=" & Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 5).Address(0, 0) & "/ $J$119*100"
        
Next: Next: Next

Skip,
[sub]
[red][/red]
[/sub]

 

[firefytr]

Why couldn't your code just be ..

Cells(i, j).Offset(k + Insert_row, 8).Formula = "=" & Cells(i, j).Offset(k + Insert_row, 5).Address(0, 0) & "/ $J$119*100"

?? Other than that, without your entire code, the full results you are receiving and the full results desired, it's really hard to tell.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[kokon]

Sorry I forgot that I have something like that

For i = 1 To 2: For j = 1 To 2: For k = 1 To 2
Cells(i, j).Offset(k + Insert_row, 0).EntireRow.Insert
Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 8).Formula = _
"=" & Cells(i, j).Offset(k + Insert_row, 0).Offset(0, 5).Address(0, 0) & "/ $J$119*100"

Next: Next: Next

 

[Zathras]

Of course, it all depends on the value of Insert_row. If it is greater than 119 all is well. If it is less than 119, then when you insert a row previous formulas will be adjusted to the new address of what had been in J119. That's just the way it works.

Perhaps it's time to tell us what it is that you are trying to accomplish.

 

[SkipVought]

Well, there you go! That answers the dilema!

Each time your INSERT, you displace the ABS REF cell by one row.

FIRST, insert the rows you need.

THEN run the code sans insert.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

...but keep in mind, each INSERT displaces your row of interest.

1) I would recommend against the inserts or

2) NAME the cell that J119 is ORIGINALLY in and reference the range name.

Skip,
[sub]
[red][/red]
[/sub]

 

[kokon]

Thx a lot SkipVought. Can you please give me little tip how to name the cell, and do reference to the range name...

 

[Zathras]

(Using a range name won't make any difference as long as the inserts are taking place above row 119.)

 

[kokon]

yes it take place above row 119. All right so I will insert all necessery rows and than insert formula

 

[SkipVought]

Z,

The SYMPTOMS ("... but it inserts $J$119*100, $J$120*100, $J$121*100 ...") indicates that row 119 is BELOW the reference point.

Use the Name Box to enter a Range Name (no spaces)

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

BTW,

Named Ranges are a powerful feature in Excel. You ought to get familiar with using them.

Skip,
[sub]
[red][/red]
[/sub]

 

[Zathras]

I meant that using a range name would not prevent the magic cell from dropping from row 119 to a lower row everytime a new row is inserted. True that all of the formulas will be "better" in that they will all reference the same absolute cell, but it won't be J119.

Without knowing exactly what kokon is really trying to do it sounded like after the macro finishes someone is going to update cell J119. That will produce unexpected results, and will happen whether or not a range name is used. (Although the results would be different.)

 

[SkipVought]

I agree that doing the INSERTS is NOT a good approch as it change the location of this TARGET.



kokon,

We relly need to know more about your application to see if there's some other way to accomplish the end result. This INSERT approch is NOT a good one.

Skip,
[sub]
[red][/red]
[/sub]

 

[jiminy]

I have always been frustrated when excel moves the cursor. I have gone into Tools/Options/Edit, and then unchecked the 'move selection after enter' box. However that will stop it from moving for everything you do. (I suppose with programs also?)