Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    207
    Thanks
    39
    Thanked 0 Times in 0 Posts

    Index & Match Problem with named Ranges

    I am having difficulty using a named range as the lookup in the match function. I have two name ranges that I use in the macro "Date" and "ExdivDate"

    The following macro works and you can see I have hard coded the lookup part of the Match function.

    Code:
    Sub DateBeforeExdiv()
        For x = 3 To 34
            Range("K" & x).Select
            ActiveCell = Application.Index(Range("Date") , Application.Match(Range("H" & x) , Range("Date"), 0)) - 1
        Next x
    End Sub
    For some reason when I preview the code above it is not correct.
    In the code after the e in Date it shows Application when it should show "), and Range ("H" & x) shows as only as Range("H

    This does not work

    Code:
    Sub DateBeforeExdiv()
        For x = 3 To 34
            Range("K" & x).Select
            ActiveCell = Application.Index(Range("Date"), Application.Match(Range("ExDivDate"), Range("Date"), 0)) - 1
        Next x
    End Sub
    The code above is not showing correctly the correct construction for Match is Match("Range("ExDivDate"),Range(
    To make sure the "ExDivDate" is correctly referenced I recorded a macro .

    Code:
    Sub Macro3()
        ActiveCell.FormulaR1C1 = "=INDEX(Date,MATCH(ExDivDate,Date,0))"
    End Sub

    And this works, any suggestions would be greatly appreciated .It seems strange that the named range "Date" works as part of the index and match functions.
    Thanks

    Peter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,141
    Thanks
    432
    Thanked 1,643 Times in 1,480 Posts
    Peter,

    Not exactly sure what the problem is with out a test workbook to use but I would however recommend that you not use Date as a range name as it is a Visual Basic for Applications key word. Not considered good coding to use key words as anything other than key words.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,131
    Thanks
    172
    Thanked 825 Times in 753 Posts
    For some reason when I preview the code above it is not correct.
    Peter,

    Are you saying that the code does not diplay correctly in th VB Editor in Excel on your computer or are you saying that when you post in WindowsSecrets, the code does display correctly in your post?

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    207
    Thanks
    39
    Thanked 0 Times in 0 Posts

    Index and Match

    Quote Originally Posted by RetiredGeek View Post
    Peter,

    Not exactly sure what the problem is with out a test workbook to use but I would however recommend that you not use Date as a range name as it is a Visual Basic for Applications key word. Not considered good coding to use key words as anything other than key words.

    HTH
    Thanks for that tip, I will change the range I named "Date" n and see if that is causing the problem. It does seem as if my problem is with the named range ExDivDate. Will post a copy of my workbook if your suggestion does not fix it.

    thanks Very Much for helping.

    Peter
    Last edited by mitchbvi; 2018-01-08 at 11:35.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    207
    Thanks
    39
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Peter,

    Are you saying that the code does not diplay correctly in th VB Editor in Excel on your computer or are you saying that when you post in WindowsSecrets, the code does display correctly in your post?
    It does not display correctly in WindowsSecrets, never had that problem before.

    Thanks

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,382
    Thanks
    4
    Thanked 238 Times in 218 Posts
    Where is that code located? If it is in a worksheet code module, all of the named ranges would have to be on that same sheet, or it would fail. It's better to either specify the relevant sheet, or use Application.Range like this:

    Code:
    Sub DateBeforeExdiv()
        For x = 3 To 34
            Range("K" & x).Value = Application.Index(Application.Range("Date"), Application.Match(Application.Range("ExDivDate"), Application.Range("Date"), 0)) - 1
        Next x
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •