Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Problem with Application.WorksheetFunction.Index

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Problem with Application.WorksheetFunction.Index

    Hi,

    I'm making changes to a pre-existing workbook with lots of VB in it.

    Three (3) of my INDEX functions work, but the 4th one fails, and I cannot work out why. Please help!


    ' This works fine
    ' Add in Resource
    ResID = Application.WorksheetFunction.Index(Sheet26.Range("A:A"), Application.WorksheetFunction.Match(Range("Data_Entrant").Value, Sheet26.Range("AA:AA"), 0))

    Sheet41.Range("B" & i).Value = ResID


    ' This works fine
    ' Add in MineID
    MineID = Application.WorksheetFunction.Index(Sheet24.Range("A:A"), Application.WorksheetFunction.Match(Range("Dayworks_Mine").Value, Sheet24.Range("C:C"), 0))

    Sheet41.Range("C" & i).Value = MineID


    ' This works fine
    ' Add in SiteID
    SiteID = Application.WorksheetFunction.Index(Sheet25.Range("A:A"), Application.WorksheetFunction.Match(Range("site").Value, Sheet25.Range("B:B"), 0))

    Sheet41.Range("D" & i).Value = SiteID


    ' THIS ONE FAILS, AND I CANNOT UNDERSTAND WHY
    ' Add in DWResource
    DWResID = Application.WorksheetFunction.Index(Sheet36.Range("D:D"), Application.WorksheetFunction.Match(Range("E" & k).Value, Sheet36.Range("C:C"), 0))

    Sheet41.Range("I" & i).Value = DWResID


  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,755
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Does the Match work?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Yes, the match works. I put a breakpoint on that line of code and checked the value of ...
    Application.WorksheetFunction.Match(Range("E" & k).Value
    ... and its "Jumbo Operator" as it should be.

  4. #4
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    I switched off DEBUG and got this message ...

    Runtime error '1004'
    "Unable to get the Match property of the WorksheetFunction class"

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,755
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Did you check the whole Match formula?

    Application.WorksheetFunction.Match(Range("E" & k).Value, Sheet36.Range("C:C"),0)
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    This portion of code ...
    Range("E" & k).Value
    ... returns
    "Jumbo Operator", as it should.

    This portion of code ...
    Sheet36.Range("C:C")
    ... says
    "Sheet36.Range("C:C") = ******** variable or With block variable not set>

    What does that mean?

  7. #7
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    I dont know whay all those ******* appeared then, but it should say the word "Object".

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,755
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Is there a worksheet with the CodeName Sheet36?
    If posting code please use code tags.

  9. #9
    New Member
    Join Date
    Nov 2011
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Yes, Sheet36 has text name "Dayworks".

    It has 6 columns:
    DWID, SiteID, InvoiceItem, DWResource, Units, Rate

    With values such as:
    1, 1, DL.1, Jumbo Operator, hour, 130
    2, 1, DL.2, Loader Operator, hour, 75
    3, 1, DL.3, Charge Up Operator, hour, 70

    The user picked "Jumbo Operator" from a populated dropdown box in Sheet35 (Dayworks Form), and I'm searching Sheet36 (Dayworks) to find the Description in column 4 so I can return the code in column 3 (DL.1) and place it in Sheet41 (Dayworks Data) ... which eventually gets sucked into a database.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,755
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Problem with Application.WorksheetFunction.Index

    Instead of using the CodeName, Sheet36, you could try using Worksheets("DayWorks").
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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