How to change a formulas result into a value once it's calculated.

[JACOBB9900]

Hello,

Can someone help me with a macro that would replace the below formula's result as a value. This formula pulls the "arrival date" of our jobs that are in route to our facility from another worksheet but once that job arrives it is removed from that worksheet along with the arrival date. If I don't manually copy and paste as a value I will have to search for the arrival date another way.


=IFNA(IF(VLOOKUP(IGT!$G86,'O:\Tiffany\JOB INFO\[2021 Backlog Tracker.xlsx]IGT Production'!$C:$L,10,FALSE)=0,VLOOKUP(IGT!$C86,'O:\Tiffany\JOB INFO\[2021 Backlog Tracker.xlsx]IGT Production'!$B:$L,11,FALSE),VLOOKUP(IGT!$G86,'O:\Tiffany\JOB INFO\[2021 Backlog Tracker.xlsx]IGT Production'!$C:$L,10,FALSE)),"")

Please let me know if you need any more information from me.

Thank you.

 

[Joe4]

This can be done via VBA, but we need to know the following:
1. What range are these formulas that you want to change to hard-coded values located in?
2. What should "trigger" this code to run, or will you be running the code manually (i.e. running the Macro, clicking a button, using a keyboard shortcut)?