+ Reply to Thread
Results 1 to 18 of 18

VBA code Error: find the records that match the criteria in a specified cell and copy

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Question VBA code Error: find the records that match the criteria in a specified cell and copy

    Hi friends!!!

    I'm new to excel as well as to this group!!!! I'm trying to code to find out the records that match the criteria in one specified cell. This code is to lookup the value entered into the cell and pull up the records that match this PR No. The code is running but unable to find and copy the records...I don't know, where i'm going wrong in the code. however, the code looks correct. Your help is most appreciated....

    For your reference, the screen shot of the spreadsheet in attachments, the columns info and code as below:

    Sl.no PR NO. Date Description Gpwy Approval Quotation Budget Remarks Pay to est. Site Rcv. ----
    1 52762 3738 73893 yes yes not paid 32832 ccc ddd 388 830200% new era gfgg
    2 63873 26/03/2013 new way yes no done over yes no yes 38739 38393aadf ww-----



    VBA code below:

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by Fotis1991; 12-23-2013 at 10:29 AM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Welcome to the forum.

    Pls take some minutes to read forum rules and specially-in this case- rule#3.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Sure, Thank you

    Please Login or Register  to view this content.
    Last edited by meer_ali; 12-23-2013 at 10:16 AM. Reason: forgot to include code tags in between code

  4. #4
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Post a sample sheet with your code.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *

  5. #5
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Thank you, fortis!!! for guiding me on this board..

  6. #6
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Quote Originally Posted by xlbiznes View Post
    Post a sample sheet with your code.
    Pls find the code that i just try to modified.I'm struggling to find the attachment icon to attach my real sheet, could you guide me...this is my first post
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Pls find the sheet in attachment
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    The pro is in column 3, not 1.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    AB33, I tried as you suggested, but have same issue. the code seems to run but unable to do the work of pulling the data of that reference column through the cell. however, i'm sending you that same sheet with the suggested modifications, and run the code from its sheet2 instead of thisworkbook sheet in VBA writing panel.

    Pls find the attachment for the file once again..

    Thank you for the efforts...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Hi,

    The problem is still not resolved, i just modified the code and tried but not solved it. could you pls look into this issue..

    the vba code is as below:


    Pls find the file copy in attachment...

    Thank you for the efforts...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Ab33,

    Are you able to run the program? pls send me the copy of the file..

  13. #13
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Yes, I can.
    I am not sure in which line of column AA you want to start copying in to. I have two codes. The first code follows your code, i.e. In to next empty row plus 3, but the problem with the code is that the code jumps 3 times every time it copies.
    The second code copies in to next empty row starting at row 22. You can change the starting row from 22 to what ever number you wish.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    AB33, Thanks for your efforts, however, I'm getting the error: Run-time error '1004': Application-defined or object-defined error.

    Abb, pls look into this error, i maybe missing any variable defining in this code... we reach some extent to this solution.. your code is atleast selecting the drop down value row.. but not able to copy because of the application defined error.
    I hope you may resolve this... Thank you once again for your nice work..


    pls test it once again the below code:

    Please Login or Register  to view this content.

    Quote Originally Posted by AB33 View Post
    Yes, I can.
    I am not sure in which line of column AA you want to start copying in to. I have two codes. The first code follows your code, i.e. In to next empty row plus 3, but the problem with the code is that the code jumps 3 times every time it copies.
    The second code copies in to next empty row starting at row 22. You can change the starting row from 22 to what ever number you wish.

  15. #15
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    meer ali,

    You must have changed the variables. I do not normally use integer as a variable.
    Where is the error on the attached? Could you please attach the sample with the code you are getting the error on?

  16. #16
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Cool Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    Great work AB33!!!! Thank you for your Generous efforts... But the 2nd code finddata1() is not working, it shows the error as: "variable not defined"... however, pls find the attached file in attachments & code within it.

    Please Login or Register  to view this content.

  17. #17
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    "variable not defined"...
    Because you have changed the variables.
    If you have Option Explicit at the top, all variables used in the code ought to be defined, otherwise you get an error.

    I have just added NR to the dim statement.


    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    12-17-2013
    Location
    Riyadh
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VBA code Error: find the records that match the criteria in a specified cell and copy

    I'm trying one more for inventory, just coded the userform, but have some errors in it..

    ABB, can we put the hyperlink into the userform window, so that when press a button say "browse" then a new window will come up and attach a file into it and and when click update, the data normally saved into the sheet as a excel record and the photo path is saved as hyperlink. when clicked on that link the photo will be pulled from the path from where it is stored.
    Attached Files Attached Files

+ 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: 2
    Last Post: 12-20-2013, 01:09 PM
  2. Find records that match criteria of multiple fields
    By jvbeats in forum Access Tables & Databases
    Replies: 3
    Last Post: 03-12-2013, 05:40 PM
  3. Replies: 1
    Last Post: 07-27-2012, 02:37 PM
  4. Match\Index to find all the records
    By itsmejan24 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-02-2012, 12:23 AM
  5. Find all records that match criteria shown in list box
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-03-2008, 05:43 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1