Results 1 to 7 of 7

Thread: Correct Qualification for Cells()
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Correct Qualification for Cells()

    Good afternoon,

    After battering my head all afternoon with various permutations of the below, I'm conceding defeat and throwing myself on the mercy of the Excel Gods
    Code:
    Dim StatsSheet As Worksheet
    Set StatsSheet = Worksheets("Stats") 
    
    Dim TransSheet As Worksheet
    Set TransBook = Workbooks.Open(TransBookName)
    Set TransSheet = TransBook.Sheets("Table")
    
    For Each MyCell In StatsSheet.Range("BR_Engineers")
          For Each MyCell2 In TransSheet.Range("Trans_Engineers")
                  If Trim(UCase(MyCell2)) = UCase(Trim(MyCell))
                        StatsSheet.Cells(Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, Range("Trans_SUPBAR_APR").Column) 
                  end if
            Next
    Next
    The Names I'm searching on in "StatsSheet" run Horizontally, and in TransSheet they run vertically, so "Totals_SUPBAR_APR" also runs horizontally and "Trans_SUPBAR_APR" runs vertically.

    So, I'm looking to transfer AND transpose values based on if the Engineer name matches.

    I really need someone to take a look at the way I've qualified the ranges in the "StatsSheet.Cells..." line, as no matter what I do, the values don't transfer correctly

    Excel errors out with "Method 'Range' of object'_Global' failed, by the way.

    Thanks in advance

    Pete

    Pete
    Last edited by NewOrderFac33; Sep 19th, 2019 at 01:01 PM.
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Correct Qualification for Cells()

    Try qualifying the named ranges with the sheet name, like
    Code:
    StatsSheet.Range("Totals_SUPBAR_APR").Row
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Correct Qualification for Cells()

    Hi, Fluff - it's 21:50 here, so I'll give it a go tomorrow - thank you.
    Pete
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Correct Qualification for Cells()

    That's fine, it's the same time here & I'm off down the pub.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Correct Qualification for Cells()

    Sorted - there was nothing wrong with my code, rather something contained within the line of code that I couldn't see (if that makes sense)
    There were a number of lines similar to:
    Code:
    StatsSheet.Cells(Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, Range("Trans_SUPBAR_APR").Column)
    all working on different ranges and it was this (the first of 6) one that was erroring (I didn't include all the lines in my original post for brevity)
    When I deleted the line of code, then built it back up, exactly the same as it had been before, it ran perfectly!
    I've encountered this before in the VBE on a number of occasions where I delete the offending line, replace it with something similar from somewhere else, or type it in again from scratch, then change it to fit and it always solves the problem.
    Code:
    StatsSheet.Cells(StatsSheet.Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_SUPBAR_APR").Column)
    StatsSheet.Cells(StatsSheet.Range("Totals_SUPBAR_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_SUPBAR_PRC").Column)
    StatsSheet.Cells(StatsSheet.Range("Totals_PRJGCM_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGCM_APR").Column)
    StatsSheet.Cells(StatsSheet.Range("Totals_PRJGCM_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGCM_PRC").Column)
    StatsSheet.Cells(StatsSheet.Range("Totals_PRJGIT_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGIT_APR").Column)
    StatsSheet.Cells(StatsSheet.Range("Totals_PRJGIT_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGIT_PRC").Column)
    I wish I knew what causes this - it's as though there are invisible characters in the line of code somewhere that cause it to error.
    I also wish I'd remembered this before I spent the best part of 6 hours trying to solve a range referencing issue that didn't exist!
    Hope you enjoyed your pint, Fluff!
    Pete
    Last edited by NewOrderFac33; Sep 20th, 2019 at 06:49 AM. Reason: Typos
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Correct Qualification for Cells()

    In your second block of code you are qualifying the named ranges like
    Code:
    StatsSheet.Cells(StatsSheet.Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_SUPBAR_APR").Column)
    which you weren't doing originally. If the ranges are workbook scope you don't need to do that, bu if they are sheet scope then you do.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Correct Qualification for Cells()

    Morning, Fluff - they are now, yes, but originally, before I added the qualifications, the first line failed but the 2nd to the 6th ones didn't (which I discovered by commenting out the first line).
    I then deleted the first line copied the second line to the first line and changed it and it all worked fine.
    This isn't the first time I've encountered this with the VBE where something errors, you either delete it and type it all in again exactly the same, or copy it from somewhere else and change it (as opposed to trying to change parts of the exiting line line, which doesn't).
    I wouldn't have thought it was possible to have hidden characters in the VBE, but that's how it appears.
    never mind, I'll just have to remember my "Tpe it in again" rule if I encounter this in the future.
    Thanks - Pete
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

Some videos you may like

User Tag List

Tags for this Thread

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
  •