+ Reply to Thread
Results 1 to 7 of 7

Problem with setting range in Macro

  1. #1
    Registered User
    Join Date
    07-13-2007
    Posts
    50

    Problem with setting range in Macro

    Hello,

    I have written the following macro that is not working as intended:

    Please Login or Register  to view this content.
    When activated, what I need the macro to do is go the worksheet "SAP Refined Data" and select the data below the cost element heading. However whenever I activate the macro from another worksheet I get the "Run-time error '1004': Application-defined or object defined error" message.

    Can anyone point out what I might be doing wrong?

    Thanks
    floridagunner
    Last edited by floridagunner; 06-21-2012 at 01:52 PM.

  2. #2
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Problem with setting range in Macro

    Hi Floridagunner,

    The problem with your code is that you're telling it to search for a value in range "1:1" - Try the following (just adjust your search range accordingly if A1 to Z500 isn't big enough);

    Please Login or Register  to view this content.

    Oh yea, forgot to also add incase you run it from another sheet - use at the begging;

    Please Login or Register  to view this content.
    Last edited by Medpack; 06-21-2012 at 01:59 PM.

  3. #3
    Registered User
    Join Date
    07-13-2007
    Posts
    50

    Re: Problem with setting range in Macro

    Hello Medpack,

    The reason I have the code searching range ("1:1") is because that is where the "Cost element" field is located.

    Secondly why do I have to specify the sheet name again when I have already specified that in the myRange attributes?

    Thanks.

  4. #4
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Problem with setting range in Macro

    Just include the sheet selection code I provided in the second code bubble. I'm not sure why Microsoft coded the vba this way but apparently the sheet needs to be the active sheet when issuing a select command.

  5. #5
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Problem with setting range in Macro

    I'm also a little confused why you use the find function when you know the location of cost elements?

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,943

    Re: Problem with setting range in Macro

    Try this:
    Please Login or Register  to view this content.
    I think you're trying to do too much with one line of code. That makes it hard to read and hard to update. Also, Who knows what "Selection" is referring to at the time you run the macro?
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    07-13-2007
    Posts
    50

    Re: Problem with setting range in Macro

    Hello Medpack,

    Thanks again for your response. The reason I use the find function for cost elements is because although the table headings are always in row 1, they are not always in the same column, that is why the macro needs to find the cost element field first before it can select the data below it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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