+ Reply to Thread
Results 1 to 7 of 7

VBA Spreadsheet Macro Problem

  1. #1
    Registered User
    Join Date
    07-21-2019
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    2

    VBA Spreadsheet Macro Problem

    Hello there,

    I have recently had an issue where the spreadsheet (attached) is experiencing processing issues. I am getting an error "Object variable or With block variable not set". This has only started occurring, and the data has not changed.

    Essentially, if you click "Data Processing Part 1" it should process and load into the DeTrack V1 sheet, from here you then highlight the yellow cell in this spreadsheet and then "CLICK TO RUN MACRO". However the above error is occurring, and the data is not being appending to this sheet after clicking Data Processing Part 1.

    I have loaded this with sample data. Any assistance much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi !

    Bad use of Range.Find method ! See the example within the VBA inner help …

  3. #3
    Registered User
    Join Date
    07-21-2019
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    2

    Re: VBA Spreadsheet Macro Problem

    Hi Marc, thanks for that. What should this be changed to?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Use an object variable to check if it matches like in the VBA help example …

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,408

    Re: VBA Spreadsheet Macro Problem

    Marc - please would you be more specific? It isn't really very helpful to constantly refer members to Help files and examples, especially if they don't know how to find them (an many won't - I don't know where the help you are referring to is).

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA Spreadsheet Macro Problem


    In fact I get another error than the one in initial post so I let any other helper to answer for the initial post error as I can't reproduce it …

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Spreadsheet Macro Problem

    When I click Data Processing Part 1 there is no data loaded into DeTrack V1; it is empty. The line of code causing the error is

    Please Login or Register  to view this content.
    The Find method therefore can't find the string it is looking for. It returns Nothing and so an attempt to reference EntireColumn produces the error.

    Working my way backwards, the code in Sub Dataprocessing is copying from Pivot Table to DeTrack V1, but the Pivot Table is empty. You have a lot of code to create a pivot table from the data on sheet Data but no pivot table is actually created, so the sheet is blank.

    I suggest you dig into the code to create the pivot table and troubleshoot that. I have not written code like that before so it would be very time-consuming to reverse engineer what you have done. If you have further questions about this code you can post them in this thread.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Replies: 0
    Last Post: 05-19-2014, 04:16 PM
  2. Macro to Export Data from one spreadsheet to a Report Spreadsheet
    By stockgoblin42 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-11-2013, 03:59 PM
  3. Macro search one spreadsheet replaces in another spreadsheet
    By sarahhe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2011, 09:15 PM
  4. Help with clear worksheet macro problem on a stock control spreadsheet
    By jilkin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2009, 02:12 AM
  5. Excel problem (not spreadsheet problem)
    By c991257 in forum Excel General
    Replies: 0
    Last Post: 05-30-2007, 11:18 AM
  6. [SOLVED] copy spreadsheet in new workbook, macro problem
    By matthias in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2006, 02:50 PM
  7. Replies: 1
    Last Post: 02-08-2005, 06:06 AM

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