+ Reply to Thread
Results 1 to 9 of 9

Trying to validate format of a cell

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Question Trying to validate format of a cell

    Hello everyone,

    I am trying to create a report that I can run at a set frequency to do some data validation. I am trying to get the correct VBA language to check the format of the string within that cell and if wrong, to then copy that row and paste to a different sheet named...errors.

    So...for example...I have data in a column....lets say Column E and the String formatting should be 5 Specific Upper case letters and then 5 numbers. So in E2 the string is DDBCA12345...and this is correct, the 5 upper case letters should all be "DDBCA". So then lets say E56 has the following string in the cell..."EXCEL12345"...this is the wrong format and I want to copy row 56 and then paste to the next empty row.

    What is the best way to accomplish this?

    Thanks!!!!!!

  2. #2
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Trying to validate format of a cell

    I have found/edited the following code...

    Please Login or Register  to view this content.
    The problem is that either I extract all of the data records or nothing happens...and that is even after I modify data in Column E to test the coding.
    I am trying to check column E that the string in that cell begins with "DDCBA" and then has numbers after that portion of the text.
    If the cell doesn't begin with "DDCBA", then i am copying the row and pasting to another worksheet.

    What am I missing to make this work correctly?

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Trying to validate format of a cell

    I would use something like this (case sensitive)

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Trying to validate format of a cell

    Im not sure I understand where to incorporate that into my coding and how to reference that it checks the correct column.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Trying to validate format of a cell

    Just put the Like comparison where you are currently putting your validation check.

  6. #6
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Trying to validate format of a cell

    Ok...so I inputted the Like comparison in the code but it did not yield any results. This was my revised coding....

    Please Login or Register  to view this content.
    So what am I missing?

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Trying to validate format of a cell

    You haven't set a value for testString.

    Try

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Trying to validate format of a cell

    This pulled everything but the errors. What is the best way to reverse this function...so instead of copying only the data that is like the criteria, I want to only copy the data that IS NOT like the criteria.

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Trying to validate format of a cell

    The Not operator should work


    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)

Similar Threads

  1. Replies: 3
    Last Post: 04-27-2015, 09:41 AM
  2. Want to use VBA to format and validate cell content
    By inputmask in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 02:16 PM
  3. Enforce a datetime format and validate it
    By MORRG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2013, 12:14 PM
  4. [SOLVED] Validate cell value- format (for use in Password)
    By jdlc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2012, 02:21 PM
  5. Enforce/Validate cell format
    By girish.ramjuttun in forum Excel General
    Replies: 1
    Last Post: 10-31-2011, 06:04 AM
  6. Input Box Help - Validate Date Format
    By Tbal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2005, 05:05 PM
  7. Replies: 5
    Last Post: 03-10-2005, 08:06 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