+ Reply to Thread
Results 1 to 10 of 10

Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

  1. 06-25-2024, 03:11 AM #1
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    MS365
    Posts
    287

    Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Dear Experts,

    I've been working with a VBA macro generated using ChatGPT that filters data based on specific criteria and retrieves additional information from an external workbook using INDEX and MATCH functions. However, I've encountered an error. I suspect the error are related to the functions.

    I hope someone here can assist me with troubleshooting this issue.

    I have attached a sample worksheet for your reference to facilitate understanding.

    Thank you in advance for your support!!!


    The steps required in the VBA are as follows:

    1. Filter Data: Show only the rows where:
    - Column AD is empty.
    - Column Z has "SMBC"

    2. Check for Data: Verify if any rows match the filter criteria. If no rows match, display a message stating that no data was found and stop the process. If there is a match, proceed to step 3.

    3. Formulas are applied to columns AD and AE to fetch data from this external workbook using INDEX and MATCH functions.
    Which is to retrieve data from columns K and M of an external workbook based on matches in column P.

    Path to an external workbook --> "R:\Shipping Group- SMBC INCOMING FUND REPORTS.xlsm".

    4. Clear Filter.

    5. Convert Formulas to Values: Convert the formulas in columns AD and AE into fixed values to prevent them from changing if the data in the external file changes.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-25-2024, 04:11 AM #2
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Hi, how's your VBA knowledge in general?
    As you have stated the code you're using is CHTGPT generated but can I assume that you made some modifications to it to suit your purposes?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  3. 06-25-2024, 04:42 AM #3
    MikeVol
    MikeVol is offline
    Valued Forum Contributor MikeVol's Avatar
    Join Date
    12-30-2020
    Location
    Odessa / Ukraine
    MS-Off Ver
    MSO Prof Plus 2021 x64 (En)
    Posts
    550

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Hi. It looks like there is no knowledge at all in VBA, just copy - paste. Try the modified code: 
    Please Login or Register  to view this content.
    Good luck.
    Last edited by MikeVol; 06-25-2024 at 04:57 AM. Reason: Update Code
    NOTE: As the original poster/owner, only you can mark your thread as SOLVED (Thread Tools above Post #1).
    You can say "Thanks" in your thread to everyone who offered to help you.
    You can also reward them by clicking * "Add Reputation" under their username on the left.
    With Regards, MikeVol.
    Reply With QuoteRegister To Reply
  4. 06-25-2024, 04:44 AM #4
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    MS365
    Posts
    287

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Hi there, Thank you for your message. My VBA knowledge is fairly basic, and I often rely on existing code samples and modify to fit my specific needs.
    Reply With QuoteRegister To Reply
  5. 06-25-2024, 04:45 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,672

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Administrative Note:

    Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

    Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

    The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 06-25-2024, 05:05 AM #6
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    MS365
    Posts
    287

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Thanks, MikeVol, for your assistance.
    However, the code not copy data from columns K and M of the external workbook to columns AD and AE of the Master Workbook.
    Reply With QuoteRegister To Reply
  7. 06-25-2024, 05:11 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,672

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    Please respond to post #5.
    Reply With QuoteRegister To Reply
  8. 06-25-2024, 05:19 AM #8
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    MS365
    Posts
    287
    Noted your messaged. Trying to figure it out now.
    Reply With QuoteRegister To Reply
  9. 06-25-2024, 05:23 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,672

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    OK - it's important.
    Reply With QuoteRegister To Reply
  10. 06-25-2024, 05:35 AM #10
    MikeVol
    MikeVol is offline
    Valued Forum Contributor MikeVol's Avatar
    Join Date
    12-30-2020
    Location
    Odessa / Ukraine
    MS-Off Ver
    MSO Prof Plus 2021 x64 (En)
    Posts
    550

    Re: Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

    The code will not copy anything! What is the search criterion in the formulas? That's right, in column H. What do you have in the cells of column H? That's right, a drop-down list. Is there a value in these drop-down lists that should correspond to column P? That's right, there are no such values. Read about the INDEX and MATCH functions then you can change the formulas to suit your needs. If you substitute the required values ​​in the cells in the H column, then everything is fine and copies correctly. See screenshots. Good luck.
    P.S.Don't pay attention to the file path in the screenshot, I was setting up the code.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Code Troubleshooting
    By Keegan1116 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2024, 08:18 AM
  2. Floor Plan Macro - Need help troubleshooting 'Run time error 9' error message
    By liddy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2021, 03:23 AM
  3. [SOLVED] Troubleshooting syntax error
    By Mr_Phil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2021, 11:08 PM
  4. [SOLVED] Combine formulas in two steps
    By lamlam28 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2019, 05:33 AM
  5. Best way to add references and clarifying notes associated with particular Excel cells
    By Anomalocaris in forum Excel General
    Replies: 2
    Last Post: 07-25-2018, 04:27 PM
  6. Autofilter vba not actually filtering - manual steps needed to filter- help
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2014, 01:36 PM
  7. troubleshooting formulas
    By freshfruit in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2014, 08:09 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1