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!

 

[PetarTen]

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.

So?
How to go around this ?I
Is it even possible?
I tried to change the name of the Cells in that particular Column.
For instance - I changed the name of the Column where the weight values appear to Table 2 ....and add a Total for all the weights in that column - well, there's no problem! It shows correctly 95 gr
After running Macro, the Total from Table2 is perfectly updated and the =SUM is normally updated, i.e. I see different value (check the screenshot)
But...when I try for a single Cell - it refuses to make it absolute ref (Cell E3 goes two Cells to the right)

 

[PetarTen]

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

Here's the file itself --> Filebin | 51jpdnvt8sprn1n0
You can easily download it. Their servers will keep it for 6 days.

 

[PetarTen]

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

Welcome to the forum btw

Can you help me mate?
I've uploaded the File on Filebin.net
Cheers!

 

[MARK858]

I've uploaded the File on Filebin.net

The file is showing errors both at the site and if you download it

 

[PetarTen]

The file is showing errors both at the site and if you download it

View attachment 118033

That IS strange!
Here everything works like a charm!
Can you propose a different Online File pool, where I can upload it?

 

[MARK858]

Did you save the file using a macro where you changed the file extension?

 

[MARK858]

Looking at your first post if you just want the formula to reference column E when you insert 2 columns try...

=(INDEX($E:$E,3)*A5)/100

 

[PetarTen]

Looking at your first post if you just want the formula to reference column E when you insert 2 columns try...

=(INDEX($E:$E,3)*A5)/100

Sub Herb1()
'
' Herb1 Macro
'

'
Range("E23:G29").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B:B,C:C").Select
Range("C1").Activate
With Selection
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B2:C8").Select
Selection.Copy
Sheets("Mix").Select
Range("D3").Select
Selection.Insert Shift:=xlToRight
Sheets("Kitchen").Select
Range("E5").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E7").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
Range("E11").Select
Selection.ClearContents
Range("E13").Select
Selection.ClearContents
Range("E15").Select
Selection.ClearContents
Range("E17").Select
Selection.ClearContents
Range("G5").Select
Selection.ClearContents
Range("G7").Select
Selection.ClearContents
Range("G9").Select
Selection.ClearContents
Range("G11").Select
Selection.ClearContents
Range("G13").Select
Selection.ClearContents
Range("G15").Select
Selection.ClearContents
Range("G17").Select
Selection.ClearContents
End Sub
___________________________________

Тhis is the MACRO code, right?

Where exactly to insert:
=(INDEX($E:$E,3)*A5)/100

 

[PetarTen]

Here's the File again....but on a different website:

https://file.io/IsJ6veWM4gwa