none
Dynamic Formula that captures the sheet name in Excel Macro RRS feed

  • Question

  • I am struggling to create a macro that references a cell on the sheet the command button is on. I can get it to work if I specifically name the sheet but then I can't copy the sheet and have the command button still work. Any ideas / help would be greatly appreciated. 

    Current Macro:

    Sub Button4_Click()

    ActiveSheet.Range("F1").Select
    ActiveCell.EntireColumn.Insert shift:=x1Down

    ActiveSheet.Range("F2").Select
    ActiveCell.Formula = "=IF(INDEX(INPUTS!$H$4:$J$79,MATCH('Example Week'!F4,INPUTS!$H$4:$H$79,0),3)="""","""",INDEX(INPUTS!$H$4:$J$79,MATCH('Example Week'!F4,INPUTS!$H$4:$H$79,0),3))"

    End Sub

    ---

    I tried changing 'Example Week'!F4 in the formula above to: CONCATENATE("'",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"!'","F4") but that game me the error: "Run-time Error 1004: 'Method' formula of object 'Range' failed"

    thank you in advance for your help.

    Friday, January 31, 2020 4:18 AM

All replies

  • Is Example Week the name of the sheet the button is currently on? If so, use

    ActiveCell.Formula = "=IF(INDEX(INPUTS!$H$4:$J$79,MATCH(F4,INPUTS!$H$4:$H$79,0),3)="""","""",INDEX(INPUTS!$H$4:$J$79,MATCH(F4,INPUTS!$H$4:$H$79,0),3))"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 31, 2020 8:28 AM
  • Dim sSearchCell As String
    
    sSearchCell = Application.Evaluate("=CONCATENATE(""'"",MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255),""'!"",""F4"")")
    
    ActiveCell.Formula = "=IF(INDEX(INPUTS!$H$4:$J$79,MATCH(" & sSearchCell & ",INPUTS!$H$4:$H$79,0),3)="""","""",INDEX(INPUTS!$H$4:$J$79,MATCH(" & sSearchCell & ",INPUTS!$H$4:$H$79,0),3))"
    

    BTW, The formula can be simplified because, it is like =If(X="","",X). You can simply use =X

    [X = INDEX(INPUTS!$H$4:$J$79,MATCH('Example Week'!F4,INPUTS!$H$4:$H$79,0),3)]


    Best Regards, Asadulla Javed

    Friday, January 31, 2020 9:11 AM
    Answerer
  • Hi,

    Considering the issue is mainly related to VBA code, to help you resolve the issue better, I will move the thread to Excel for Developers. You may get more helpful replies there. Thanks for your kind understanding. 

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, January 31, 2020 9:13 AM
  • To:  Sean___M
    re:  get worksheet name

    This returns the worksheet name...
      Dim shtName As String
      shtName = ActiveCell.Parent.Name
    '---

    The new (free) Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell or every row in two worksheets - choice of compare types)
    Download from (no ads):
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Saturday, February 1, 2020 4:51 AM