+ Reply to Thread
Results 1 to 3 of 3

Save record depending on cell values and if record already exists

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Save record depending on cell values and if record already exists

    I want to save a range of data (X8:bz8) into an array on another sheet programmatically. When the code is run, I want it to do one of three things depending on the value of two cells. I can easily express it as a formula but I'm battling to get the code right. Assume the cell values are

    1. Cell "A1" (will show TRUE or FALSE), and
    2. Cell "V6" (will contain a string which is a unique identifier).

    Condition 1: If A1 = False and V6 already exists in a range (say C2:C5000 on another sheet), overwrite the existing data in the array with the data in X8:BZ8

    Condition 2: If A1 = False and V6 does not exist in the range C2:C5000, append the data to the end of the array

    Condition 3: If A1 = TRUE, show a message box saying 'the data exists and the record is closed', and do not amend or append the data from X8:BZ8 anywhere. (This should probably be the first condition tested.)

    The code I have so far is:


    Please Login or Register  to view this content.
    Any help is much appreciated.

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Save record depending on cell values and if record already exists

    Hello
    I created a mock-up workbook and your code worked fine with a couple of amendments.

    Please Login or Register  to view this content.
    The line marked red for the last row used argument was for column B (2), I changed that to C (3). I also added the A1 TRUE/FALSE test with message box.

    Does this work for you?

    DBY

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Re: Save record depending on cell values and if record already exists

    Thanks for your help - it all works well. Rep added. Your help is appreciated.

+ 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. Save a Permanent Record of Previous values
    By marvslater in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2013, 02:46 PM
  2. [SOLVED] VBA to record cell reference values as text and record change
    By Katie O in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2013, 12:14 PM
  3. Replies: 1
    Last Post: 11-16-2012, 09:25 AM
  4. Command Buttons for Viewing Record, Next Record and Previous Record
    By david1987 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2012, 06:30 AM
  5. Goto Record when Duplicate Record Exists
    By rlsublime in forum Access Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2012, 11:13 AM
  6. Help with vba, if record exists overwrite ?
    By vsantoro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2011, 01:12 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