Pass Cell Address into Formula

[nalij_bond]

Thanks all in advance for your time and help.

My Issue: I am trying to define a variable and use that value to append to a formula within a cell

Current Code:

Sub test3()
'In the PoC Doc Template, use the address function to find the cell reference column in row 10.
'for the term "VHI PMO/GTM". Then in the Sheet 'Key Stakeholders' in cell H6, use the cell
'reference found and append to the formula'

    Dim VHI_Ref
    
    VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

    Sheets("Key Stakeholders").Range("H6").Formula = "='PoC Doc Template'!" & VHI_Ref

End Sub

Expected Result: What I would like to happen is in the 'Key Stakeholders' worksheet, in cell H6 - a formula like "='PoC Doc Template'!L12" is created.

Current Result: As of now, I keep receiving a Debug Error on the Sheets("Key Stakeholders").Range("H6").Formula = "='PoC Doc Template'!" & VHI_Ref line of code.

Any help would be greatly appreciated!

 

[Micron]

I think it's the single quotes and the missing double quote at the end. Try
Sheets("Key Stakeholders").Range("H6").Formula = "=PoC Doc Template!" & VHI_Ref & """"

 

[nalij_bond]

I think it's the single quotes and the missing double quote at the end. Try
Sheets("Key Stakeholders").Range("H6").Formula = "=PoC Doc Template!" & VHI_Ref & """"

Thank you for taking the time to reply.

Unfortunately, that generated a Run-Time Error '1004' on your suggested line.

I've tried a few variants like,
Sheets("Key Stakeholders").Range("H6").Formula = "='PoC Doc Template'!" & Sheets("Key Stakeholders").Address(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))

But that gives me a "Compile Error: Syntax Error"

 

[nalij_bond]

OK. Getting somewhere....

With the following code

    Dim VHI_Ref
    
    VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

    Sheets("Key Stakeholders").Range("H6").Formula = "=""PoC Doc Template!"" & VHI_Ref"

In Worksheet("Key Stakeholder"), cell H6... it does add in a formula but the formula listed is "="PoC Doc Template!" &@ VHI_Ref"

So, it's not passing the Address that it should have found....and appending to the text portion to make the desired output of "='PoC Doc Template'!L12"

 

[Micron]

1004 is the most useless error number given that there must be at least 6 messages that go with it. The message matters more in that case.
You got a different error with that then you had before, so you made progress.
You get VHI_Ref in the formula because it's within the beginning and ending quote - & VHI_Ref" Excel is adding @ because it's a new thing for range formulas.

 

[nalij_bond]

1004 is the most useless error number given that there must be at least 6 messages that go with it. The message matters more in that case.
You got a different error with that then you had before, so you made progress.
You get VHI_Ref in the formula because it's within the beginning and ending quote - & VHI_Ref" Excel is adding @ because it's a new thing for range formulas.

My apologies for not being explicit on precisely what the error dialogue is.

When I run your suggested code modification,

    Dim VHI_Ref
    
    VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

    Sheets("Key Stakeholders").Range("H6").Formula = "=PoC Doc Template!" & VHI_Ref & """"

Here is the error received

The debug takes me to the last line Sheets("Key Stakeholders").Range("H6").Formula = "=PoC Doc Template!" & VHI_Ref & """"

Again, I appreciate your assistance in helping to resolve this. I've spent far too much time trying to get this to work.

 

[nalij_bond]

@Micron

I also attempted an alternative approach...

With the following code,

    Sheets("Key Stakeholders").Range("T6").FormulaR1C1 = _
        "=ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

I can successfully see the correct cell reference in Sheets("Key Stakeholders").Range("T6") of $L$12

The part I get stuck on is how to use the referenced cell value in T6 and append it to the "=PoC Doc Template!" & portion of the formula.

So the end result in Worksheet("Key Stakeholder"), cell H6 becomes "='PoC Doc Template'!L12"

 

[Peter_SSs]

So, it's not passing the Address that it should have found.

I think that your problem is that an address has not been found. Can you explain in words exactly what you think the following line should be doing?

VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

 

[nalij_bond]

I think that your problem is that an address has not been found. Can you explain in words exactly what you think the following line should be doing?

VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"

I need to search in row 10 of the worksheet 'PoC Doc Template' to find which column contains the value "VHI PMO/GTM". Once the column is identified, my row will always be 12.

So I am trying to pass that reference (correct answer of $L$12) into a cell formula on a different sheet ('Key Stakeholders').

Ultimately, creating a formula in the 'Key Stakeholders' worksheet, in cell H6 of "='PoC Doc Template'!$L$12". This formula reference would return the final value I need.

Thanks for your help!

 

[Peter_SSs]

Thanks. Try making this change

VHI_Ref = "ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!R10,0))"
VHI_Ref = Evaluate("ADDRESS(12,MATCH(""VHI PMO/GTM"",'PoC Doc Template'!10:10,0))")