+ Reply to Thread
Results 1 to 10 of 10

Match or Loop to Transfer Data from Userform to Sheet

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Match or Loop to Transfer Data from Userform to Sheet

    I have two sheets: INVOICES sheet has a list in column B of ID numbers say 1000-3000 and LineItems sheet which is empty.

    Currently, users enter in pending invoice information into the userform (including an ID number) and then click send and the information is entered into the INVOICES sheet based on the ID number (which is preexisting on the INVOICES sheet) and also onto the LineItems sheet into the next empty row. This works just fine. Where I am having trouble is once the invoice is approved, the send button enters in the approval date and "Approved" in the correct column of the corresponding row of the INVOICES sheet (again using the match function based on the ID number) but I cant figure out how to enter the approval date and "Approved" in the corresponding row of the LineItem sheet since the ID number is not preexisting. I have tried to loop through the LineItems sheet looking for the entered ID number, but it's not working. Maybe someone can help? Below is a bit of the code I am referring to. Many thanks to anyone who can help me with this issue I have been struggling with for days!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Hi

    How about an example file. I'm not sure if the ID number is really a number, or text (does it have any alpha characters? or has it been entered as text from the textbox?). It may be you need to format the IDNumberBox.value to numeric when you are doing your search.

    rylo

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Match or Loop to Transfer Data from Userform to Sheet

    I have attached a sample spreadsheet. Some things I needed to alter some things (such as email addresses) for security reasons so the email function is not working as it usually does, but everything else is essentially the same.


    ExcelApproved.xlsm

  4. #4
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Also, the ID number is entered as text from the textbox in the userform.

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Hi

    To save me trying to work through all the code, and the steps, when / how would the entry from the form be put onto the sheet Lineitems? I can see where you are trying to find it, and of course, if it doesn't exist then it won't be found. So when does it get created on this sheet?

    rylo

  6. #6
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Match or Loop to Transfer Data from Userform to Sheet

    The entry is initially put onto the LineItems sheet when 'pending approval button' is selected and the save button is clicked (this code is located about 2/3 of the way down on the UserForm1 code page).

    What I don't understand about the loop that I've been working on is that the ID numbers are already on the LineItem sheet if the invoice has been approved, so why isn't my code able to look through the LineItems sheet for the matching ID number and enter in the approval date and "approved" in the corresponding row? Even though there are multiple lines on the LineItems sheet with the same ID number, it should be able to step through and find them all, shouldn't it?

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Hi

    I did a really quick check, and I couldn't see anything in the pending section that would put a new entry into the Lineitems sheet when save and send was clicked with the pending approval button selected. I put in some quick dummy information for ID 1004, but nothing was added.

    Are you sure you have the most current code, with that part working in the example file?

    If it is, can you give full details of what you put where to make it put the new entry into Lineitems.

    rylo

  8. #8
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Yes, this is the most current code. I too tried some dummy info for ID 1004, and it worked after about 30 seconds. Because I changed the email addresses for security reasons, when send is clicked it takes a little longer than usual for the info to be entered into the sheets.

    Here is the code for entering in the info on the LineItems sheet when invoice pending button is selected and Save button is clicked:

    Please Login or Register  to view this content.
    As you can see, when the invoice is pending, the code finds the next empty row in LineItems and enters the data from the userform into that row. But when the invoice is approved, I need the code to search through the rows of the LineItems sheet and find each row with the matching ID number, and enter in the approval date and "Approved" in the correct cell. Does this make sense? I really appreciate your commitment to helping me try and figure this out, rylo!!

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Match or Loop to Transfer Data from Userform to Sheet

    Hi

    Just realised, that your loop is starting from cell A1 which is blank, so it doesn't go any further. That is why it is not finding things.

    Rather than using a loop, try using find to get the item. Much faster.

    rylo

  10. #10
    Registered User
    Join Date
    04-27-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Match or Loop to Transfer Data from Userform to Sheet

    I decided to try a for-if statement and alas- it works! Here's my edited code for anyone who is interested.. Thank you again rylo for your guidance.

    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)

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