VBA - Find and Replace Using Cell Reference on separate tab

[MrSourApple]

I am trying to find and replace part of a formula using vba by referencing the value of a cell.

What I need to do:
Find - Value of cell E10 on tab name "Admin"" within the formula in cell E8 of the "Submission Comparison" tab
Replace - the string within the formula with value of cell E9


Specifics:
Cell E10 on the tab "Admin" contains "_17Official" (this cell runs on a formula and is updated regularly)
Cell E9 on the tab "Admin" contains "_17Passback" (also updated regularly)

Cell E8 on the tab "Submission Comparison" has the formula:
=IF(Admin!$E$6="Formulation",IFERROR(SUMIFS(PY_17Official,UII_17Official,$A16........)

I would like to use vba to replace the "_17Official" with "_17Passback", however, I need to to be based on the tab/cell reference so it can be updated regularly.

Thank you!!!

 

[StephenCrump]

Assuming you always want to use the value of Admin!E9 (?) you could do something like this:

s = Sheets("Admin").Range("E9").Value
Sheets("Submission Comparison").Range("E8").Formula = _
    "=IF(Admin!$E$6=""Formulation"",IFERROR(SUMIFS(PY" & s & ",UII" & s & ",$A16........"

 

[MrSourApple]

Thank you!
You are correct that I always want to use to value of Admin!E9. I tried this out and it worked well.

 

[StephenCrump]

Thanks for the feedback. I'm glad it worked for you.