+ Reply to Thread
Results 1 to 16 of 16

Referring to columns by header in code, error 1004 method range of object global failed?

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Referring to columns by header in code, error 1004 method range of object global failed?

    Hi all,

    Code:

    Please Login or Register  to view this content.
    Just debugging at the minute so more issues may come up, but at the minute

    DictKey = Join(Range ... etc.) is the row highlighted, saying Run-time Error 1004, Method 'Range' of object '_Global' failed...

    I really would like to refer to the table columns by their headers as it would make sure it doesn't matter if the data it is in workbook column A B or so..

    Please help?
    Attached Files Attached Files
    Last edited by LIL2606; 02-11-2019 at 03:41 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Hi,

    Exactly what is the goal here?
    I don't mean explain what your macro is doing, or trying to do, I mean what's the rule for grabbing a subset of data from the data table.

    I'm not entirely sure you need a macro it may be that a Pivot Table with some Slicers to filter it would be sufficient. Even if a Macro is needed it would seem a Data Filter macro would probably be simpler.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    I need to delete duplicate-triplicate rows based on data from 3 columns.. First name, Surname and Fruits, and I have other columns (two in the example) that should have no influence on which rows get deleted, (in my real data set about 20).
    I am using a macro as it is just a part of the whole data clearing that I do on a massive table, so I will have a lot more to do before it can be used for a pivot table report, I am just getting rid of the unnecessary data at the minute.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Where there are duplicates but the basket size is different what determines which ones you lose, or doesn't it matter?

    e.g for John Smith Cherry why do you show the 05 basket and not say the 03 basket record?

    Personally I'd use two helper columns and a data filter to show the records you don;t want and delete them - within and by a macro if necessary
    e.g. F2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    G2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then autofilter column G for values >1. In a macro use the
    Please Login or Register  to view this content.


    However this is clearly a much simplified example and doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.

  5. #5
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    I have other columns (two in the example) that should have no influence on which rows get deleted
    No it doesn't matter which basket stays, and in my original sheet I have another 20 columns that have different data that doesn't matter, and about 600 rows. It would take forever to go through my real data set and filter it manually and it is also confidential, and the above code does the job without having to introduce helper columns.. So I don't need another solution.

    Thank you for your time and efforts, but can you please help me debugging the code I posted?

  6. #6
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    This works
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Thank you, could you explain some of it for me please? as I'm still trying to learn VBA.

    Please Login or Register  to view this content.
    seems like it's only Dict and DictKey had been used from this bit?Or did I just miss the others?

    Please Login or Register  to view this content.
    is that x means: Dim x as Long?

    And basically in this code.. the "For each" got replaced by another loop which is stepping backwards?

    Please Login or Register  to view this content.
    I couldn't figure out still what was Application.Index and what is Application.Transpose? Generally this code line I don't really understand.. Could you help me understand it?

    Please Login or Register  to view this content.
    And what does this bit do?

    Link to my other posted thread (marked solved): https://www.excelforum.com/excel-pro...y-headers.html

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    1. First part - you are correct, others were left over from your original code
    2. If not named explicitly then a dim reverts to variant so i as Long is a number/integer and x is variant as an array
    3. correct about Loop
    4. When you need to use an inbuilt function Application.Index is the Index function, same as transpose
    5. Last bit of code just prints your dictionary entries to the immediate window in VB editor for checking.

  9. #9
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Thank you! But why is transpose used twice, and the ", , x" part? Omitting something, if so what?

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286
    Quote Originally Posted by LIL2606 View Post
    Thank you! But why is transpose used twice, and the ", , x" part? Omitting something, if so what?
    There is a thread here that answers both I read today. Two transpose iirc turn a one column 2d array to 1d. Like all functions in VBA excel ,, means revert to initial or default setting. I'm learning as you are and it pays to look at things in small bites. If you are trying to follow through a code use message boxes or debug.print to check is that what you expected. I spent a day at your original posted code and found no answer to getting it to read what I wanted. Just means sometimes it's not a dead end....just a sharp turn left. If I find thread I'll post here. It was on the lines of what not to do in writing VBA.

    If this has answered your original question can you mark as solved
    Last edited by nigelog; 02-14-2019 at 04:54 AM.

  11. #11
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Thread was " what to avoid in VBA" posted yesterday. Worth reading the basics and the double transpose appears 4 or 5 pages in. Still haven't read it all myself but I will get around to it.

  12. #12
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Thank you so much for this! I have read through the thread you suggested as well, but it's not exactly obvious for me why transpose is necessary.. I'll mark this solved, as I got an answer, but will post a new thread about transposing. I'll link this thread in it as well.

  13. #13
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Glad to help, thanks for marking as solved and rep points, appreciated

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    When you use Join function, it only accepts 1D array.

    1) .ListRows(i).Range is a 1 based 2D array, so you need to convert it to 1D array with Transpose function x 2, or Index function.
    2) when you need to get part of the array, Index function enables you to do it.

  15. #15
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    Here is the thread I opened with the question about transpose:

    https://www.excelforum.com/excel-pro...ml#post5065109

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Referring to columns by header in code, error 1004 method range of object global faile

    I've just posted such lines just because you wanted to rewrite your original code without showing what you are trying to do.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime error 1004 Method 'Range' of object_ Global Failed
    By stevoDE in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-30-2017, 11:33 AM
  2. Error Method Range of Object - Global Failed
    By bdrod in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2016, 06:32 PM
  3. Run Time error 1004 method sheets of object global failed
    By bevc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-27-2016, 04:09 PM
  4. [SOLVED] Error - Method 'range of object' - Global failed
    By highlystrung in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2015, 06:18 AM
  5. Run-time error '1004': Method 'Range' of object 'Global' failed
    By djwestholm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2012, 04:52 PM
  6. Run-time error '1004': Method 'Range' of object '_ Global' failed
    By mdvc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-20-2011, 11:48 AM
  7. runtime error 1004 method range of object global failed
    By dreamz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2006, 03:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1