+ Reply to Thread
Results 1 to 11 of 11

Replace data in excel row using a lookup and macro

  1. 06-09-2009, 04:42 PM #1
    Jay2512
    Jay2512 is offline
    Registered User
    Join Date
    06-09-2009
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003 / 2007
    Posts
    4

    Unhappy Replace data in excel row using a lookup and macro

    Hi all,

    I have been working hard to create a new quote tool for the guys to use at work. It comprises of 3x sheets. sheet 1 (quote entry) is used to gather the information i.e, quote number - cost - customer and so on. I have then used a macro to copy and past this data into corisponding cells on sheet 2 (quote log). the macro put the data in this sheet, clears the contents of the cells in the data enty and generates a new quote number. Sorted.

    Sheet 3 is a quote update page, on this sheet you can enter the quote number press enter and bring up all the information on the quote log that corisponds to that quote number. I have achieved this using a VLOOKUP formula to populat the data. The guys have a number of new cells nominated to input changes to the retrieved data, i.e. cost - date and postcode. As in sheet 1 (quote entry) i have created a macro to return the data to sheet 2 (quote log) but it save as a new quote using the old number this leaving me with duplicate information.

    Does anyone know how i can get the updated data to replace/overwrite the old data, ensuring the quote numbers stay in order and are not duplicated ?

    Below is the macro i am using, along with a generic set up of my quote log.

    Please Login or Register  to view this content.
    And the sheet:

    L/UP ref Code TYPE Quote No. Status
    517 HM Opp Q51709EP Live
    518 HM Opp Q51809EP Live
    519 HM Opp Q51909EP won
    520 HM Enq Q52009EP Won
    521 HM Enq Q52109EP Live
    522 HL Enq Q52209EP Live
    523 HL Enq Q52209EP Won
    Last edited by rylo; 06-09-2009 at 09:32 PM.
    Reply With QuoteRegister To Reply
  2. 06-09-2009, 09:34 PM #2
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Replace data in excel row using a lookup and macro

    Hi

    How about an example file - much easier for us to see what you are working with....

    Also, can you please review the forum rules about wrapping code with the code tags. I've edited your post for you this time.


    rylo
    Reply With QuoteRegister To Reply
  3. 06-10-2009, 02:35 PM #3
    Jay2512
    Jay2512 is offline
    Registered User
    Join Date
    06-09-2009
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003 / 2007
    Posts
    4

    Re: Replace data in excel row using a lookup and macro

    Hi,

    Please find an attachment of the data sheet i have problems with.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-10-2009, 06:05 PM #4
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Replace data in excel row using a lookup and macro

    Hi

    and the password is.....

    rylo
    Reply With QuoteRegister To Reply
  5. 06-11-2009, 03:39 PM #5
    Jay2512
    Jay2512 is offline
    Registered User
    Join Date
    06-09-2009
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003 / 2007
    Posts
    4

    Re: Replace data in excel row using a lookup and macro

    Hi Guys,

    Sorry my head is all over the shop, password is PWHEELER09
    Reply With QuoteRegister To Reply
  6. 06-11-2009, 06:57 PM #6
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Replace data in excel row using a lookup and macro

    Hi

    Didn't like it. I tried both a copy / paste, and complete type and it didn't work. Is the case provided correct?

    rylo
    Reply With QuoteRegister To Reply
  7. 06-12-2009, 11:38 AM #7
    Jay2512
    Jay2512 is offline
    Registered User
    Join Date
    06-09-2009
    Location
    Cannock, England
    MS-Off Ver
    Excel 2003 / 2007
    Posts
    4

    Re: Replace data in excel row using a lookup and macro

    Hi Rylo,

    Please find a new version, password is Capital case PWHEELER09
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-16-2009, 06:56 PM #8
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Replace data in excel row using a lookup and macro

    HI

    As the number already exists, you can easily find the row that contains that quote number.

    Please Login or Register  to view this content.
    This will give the row that has the details

    Then you can do something like

    Please Login or Register  to view this content.
    This will replace the existing data with the value paste from row 50 of the current sheet.

    HTH

    rylo
    Reply With QuoteRegister To Reply
  9. 10-11-2010, 02:12 PM #9
    nhannigan
    nhannigan is offline
    Registered User
    Join Date
    10-08-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Replace data in excel row using a lookup and macro

    I am having a similar issue and came across this forum after some searching for what I wanted. It seems like a good solution to what I am doing, however, I am fairly new to VBA and I am having a little bit of a hard time understanding what the code is telling me in your answer, rylo. I apologize if it is "simple" stuff, just trying to understand it to get a better idea of how to fix my issue.

    I too have a database that I am building for a production floor and need to have it store data that is entered from a worksheet. However, the guys on the floor don't fill out all the data in one day and need to go back and input more. So, I have set it up so that they can search for the data using VLOOKUP and then update the data using control buttons. I just need to know how to update the data without creating a whole new entry as above. Thank you for your help in advance.

    Nolan
    Reply With QuoteRegister To Reply
  10. 10-11-2010, 06:12 PM #10
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Replace data in excel row using a lookup and macro

    Nolan

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

    rylo
    Reply With QuoteRegister To Reply
  11. 10-12-2010, 11:21 AM #11
    nhannigan
    nhannigan is offline
    Registered User
    Join Date
    10-08-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Replace data in excel row using a lookup and macro

    I apologize for the inconvenience this may have caused and thank you for replying.
    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)

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