Macro which changes the inside formula

[PetarTen]

Hi Folks, a newbie here...
I made a Macro that Kenji did here:

It was fun building it until I ran into something annoying - every time after execution the Table would shift one row to the right, which CHANGED the Reference within the Formula (that I wrote in the nearby table)
Please look at the attached pic!
After the Run, the cell E3 has been moved to the right and changed to G3
Why is this happening, after I purposely "told" Excel, to LOCK that particular cell by typing $E$3 (you can see it)
What else do I need to change?
BTW, if you ask me, "Have you tried running the Macro with 'Relative Reference On/Off' - YES, I have!"
Please if anyone here knows how to work around this problem - I would greatly appreciate it!

 

[dreid1011]

Can you post the code you ended up with as well?

 

[PetarTen]

It's visible on the pic:

=($G$3*A5)/100
whereas it should be:
=($E$3*A5)/100

...and always $E$3

 

[MARK858]

Can you post the code you ended up with as well?

It's visible on the pic:

=($G$3*A5)/100
whereas it should be:
=($E$3*A5)/100

That's not code, that is a formula, @dreid1011 is after the VBA code that you are currently using

Welcome to the forum btw

 

[PetarTen]

Oh yes, sorry for my mistake
Tonight I’ll be in front of the comp again .
Have a great day further…

 

[PetarTen]

Can you post the code you ended up with as well?

Sub Herb2()
'
' Herb2 Macro
'

'
ActiveCell.Offset(18, 0).Range("A1:C7").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -1).Range("B:B,A:A").Select
ActiveCell.Offset(0, -1).Range("A1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 12
ActiveCell.Range("A1:B7").Select
Selection.Copy
Sheets("Mix").Select
Selection.Insert Shift:=xlToRight
Sheets("Kitchen").Select
ActiveCell.Offset(-18, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(-12, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveCell.Offset(-12, -2).Range("A1").Select
ActiveCell.FormulaR1C1 = "dhndf"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "fdhndfhbdf"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "dfb fgb"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "fgb fg"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "dfbg fgb"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "bgf fg"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "dg dg "
ActiveCell.Offset(-12, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "15"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "25"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "35"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "45"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "55"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "65"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "75"
ActiveCell.Offset(-12, -2).Range("A1").Select
Sheets("Kitchen").Select
ActiveCell.Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(-12, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.ClearContents
End Sub

Here you go mate!

 

[dreid1011]

I don't see where this code is inserting the formula in question. Is it copying an existing formula? What exactly is the code supposed to do?

 

[PetarTen]

I don't see where this code is inserting the formula in question. Is it copying an existing formula? What exactly is the code supposed to do?

The code must automate the arranging 1 to 7 names to their weights. The problem is that the additional Sparkline that I’ve included on the Sheet2 and which takes the weight value constantly moves its Referenced Cell to the right (two columns to the right) because after Macros Run, both names and weights occupy two horizontal cells.
I can make a short video and upload it on YuouTube

 

[PetarTen]

I don't see where this code is inserting the formula in question. Is it copying an existing formula? What exactly is the code supposed to do?

The Reference Cell must always stay on its place not shifting to the right after Macros Run

 

[dreid1011]

If I am not mistaken, your formula is changing because of this:

Sheets("Mix").Select
Selection.Insert Shift:=xlToRight

It doesn't matter if you use absolute cell reference to lock it, the formula will update the column/row when you insert cells or move the referenced cell.