+ Reply to Thread
Results 1 to 8 of 8

Userform - Adding Data to Existing Rows

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Userform - Adding Data to Existing Rows

    What I currently have working (it's a working progress still to be tweaked) is a Userform for inputting data into relevant rows (inputform).
    What I now need and have almost got working is another Userform for adding in some additional information and preferably overwriting any existing data (updateform).

    I'm very new to VBA and have been doing my best to get my head around it but can't say I completely understand all the terms, I work with taking existing code I have found elsewhere and editing it to best suit my needs. This means a lot of fiddling to try see exactly what each line of code is doing. With my current task I've about reached my limit and am now well and truly stuck on how to fix this issue. In short, there may be a lot of irrelevant/incorrect lines of code that I haven't realised are irrelevant/incorrect.

    So, to the issue in hand.

    I will have lots of unique contract numbers (column A) that I will be using to bring up the details in the update form.
    I have the client name (column D) which is currently serving as a check to make sure I have brought up the correct contract.
    Then I have 7 more boxes to be filled out with the new data that I want added to existing rows (Columns AG,AM,AN,AO,AU,AV,AW)

    I have a piece of code that I thought should work but it just adds the data to a new row and I can't seem to figure out why it is doing this or how to get it to instead write in the existing rows. (Update Button)

    Please Login or Register  to view this content.
    The only thing that I can think of that would be causing this issue (if it's not part of the above code) is the piece of code that brings up the information to begin with. (Find Button)

    Please Login or Register  to view this content.
    Please let me know if I've missed out any relevant data!
    Attached Files Attached Files
    Last edited by Mousiefuzz; 04-30-2015 at 04:09 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Userform - Adding Data to Existing Rows

    Example attached.
    Last edited by davesexcel; 04-29-2015 at 07:48 AM.

  3. #3
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: Userform - Adding Data to Existing Rows

    Done, hope that helps.
    Thanks for the advice and instructions on how to do so.

    I feel like I'm missing something blindingly obvious about the code I've used but can't for the life of me find out why it's still acting up.
    Last edited by Mousiefuzz; 04-29-2015 at 11:31 AM.

  4. #4
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: Userform - Adding Data to Existing Rows

    Bump no response

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Userform - Adding Data to Existing Rows

    Let me guess (didn't test)...
    First find last row.
    Then find match (I assume Contract).

    if match is not found you'll got error.
    Because of On Error Resume Next part it will remain on last row.

    In other words: it will put data in last row if match is not found.


    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Userform - Adding Data to Existing Rows

    I think this is the problem:

    Please Login or Register  to view this content.

    You loop through looking for a match with the contract number and, when you get one, you update the client name. However, you then continue to loop through the data to the lastrow ... hence the currentrow will always equal the lastrow. I think you just need to add an Exit Sub when you find the client you are looking for.

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: Userform - Adding Data to Existing Rows

    Zbor - Unfortunately it was putting it in the last row whether or not a match was found. I tried using your changes and it was still posting on the last row whether there was a match or not. I appreciate the help though.

    TMS - I inserted those two words into the code and what an amazing difference it makes when you know where to put them!
    It works perfectly now, updating the row exactly as I want it to! My next challenge is to take what you've helped me with and apply it to my first userform so information can be updated in that too, wish me luck!
    Thanks also for helping me understand the code I used, didn't realise it wasn't stopping it's search after finding a match, though it makes sense once I think about it. You've been of massive help and I can't thank you enough!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Userform - Adding Data to Existing Rows

    You're welcome. Thanks for the rep.

+ 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. [SOLVED] Adding two rows after every existing row
    By Hitch75 in forum Excel General
    Replies: 11
    Last Post: 10-15-2014, 03:07 AM
  2. Need help with USERFORM adding extra data to an existing list
    By fur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2014, 08:25 AM
  3. [SOLVED] Adding new sheet to existing userform
    By darkangl640 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2013, 12:49 PM
  4. Adding Code to an Existing UserForm with VBA
    By JimmyA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2010, 12:02 PM
  5. Userform problem adding to existing rows of data
    By glenness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-20-2007, 09:59 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