indirect formula syntax problem

    hi guys.

    my sheet name is store in D1. if i record a macro i get the right information the formula is as follows

    Code
    ws.Range("d3").Formula = "=INDEX(INDIRECT(""'""&R1C&""'!""&""1:2""),2,MATCH(RC3,INDIRECT(""'""&R1C&""'!""&""1:1""),0))"


    i wanted to use a different way to find it, using variables so i used the following formula which doesnt work , some syntax issue which i am not able to figure out


    Code
    Dim drng As Range
Dim ws as worksheet
Set ws = Worksheets("Staff")
Set drng = ws.Cells(1, 4)

ws.Range("d3").Formula = "=INDEX(INDIRECT(""'"" & drng.Address(True, False) & ""'!""&""1:2""),2,MATCH(C3,INDIRECT(""'"" & drng.Address(True, False) & ""'!""&""1:1""),0))"


    i also tried to remove 1 set of speech marks from either side of & but i am stuck, any help would be greatly appreciated

    Hi,


    Just to allow for testing ... would you mind attaching a tiny sample file ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks for your sample file


    There are several issues which could be tackled regarding your workbook structure..., But ... if we only focus on your central question :


    With a worksheet formula, in order to get the value located in cell D1, you would need =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"1")


    As a consequence, again with your current logic, in VBA, this would translate as follows :

    Code
     Range("D4").Formula = _
        "=INDEX(INDIRECT(""'"" & INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&""1"")& ""'!""&""1:2""),2,MATCH(RC3,INDIRECT(""'"" &INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&""1"")& ""'!""&""1:1""),0))"

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Haha 1

    Thanks a lot for your Thanks 8)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    As far as your workbook is concerned, the structure you have selected is very probably not simplifying your life ....


    With a worksheet tab for each and every day, you are not taking advantage of the benefits of a flat DataBase ... which can complicate the following steps of your Analysis with, for example, Pivot Tables ...

    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Haha 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login