+ Reply to Thread
Results 1 to 11 of 11

How to adress any text and automatically exit the code?

  1. #1
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    How to adress any text and automatically exit the code?

    Hi,

    So, I was wondering how you can address any text in a cell and then exit the code.
    Exit the code part is not hard, but I have no idea how to exit a cell that contains letters or signs like: "afdhfudshuifdshif" or "#&!/(¤&("!%¤(/")#0"

    Thing is that my code will stop function if I get letters or signs in one of the cells in a certain range.

    I thought that the code may be something like:

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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to adress any text and automatically exit the code?

    One way

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    Re: How to adress any text and automatically exit the code?

    Thank you for the reply, but the code does not work, it shows the message even though the cells only display numbers.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to adress any text and automatically exit the code?

    Are you sure your 'numbers' are in fact numbers and not text that looks like a number.
    Test each one with the =ISNUMBER()) function. i.e. in F3 enter =ISNUMBER(F2) and copy across to Q2. Do they all return TRUE

    The COUNT() function returns the number of numbers, which in you case should be 12.

    Otherwise upload the workbook since it's impossible to diagnose with sight of your workbook.

  5. #5
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    Re: How to adress any text and automatically exit the code?

    Ahh, I see.

    I did the =Isnumber() on all the cells, and it came back true.

    Maybe I was not clear enough in the first post, but what I want to do is that I want the code to stop if someone posts number in the sheet cells instead of numbers because the code will show error if you try to run it with letters or weird signs in the cells.

    So I basically just want the code to search for letters or signs and exit the code if they appear in the named cells.

    The sheet is so big and confusing now, I really do not want to upload it.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to adress any text and automatically exit the code?

    How about

    Please Login or Register  to view this content.
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    Re: How to adress any text and automatically exit the code?

    Thanks!

    This code actually did the same. Is there a way to get an "if" and "end if" at the beginning and end of this code? I just want the textbox to show if there are text or signs in the code.

    Remember this is just part of a longer code and I just want the text to show if the code has text or signs. If not, the code should just proceed as normal.

    Thank you for your interest!

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to adress any text and automatically exit the code?

    I don't understand why you're saying that. I presume your use of the words text box was a typo and you really meant message box.

    In both examples the message box pops up if there is NOT a number in all 12 cells. i.e when it pops up there is at least one piece of text in the F2:Q2 range.
    What you're asking for is already there as far as I can see.

    The other option of course is to trap a non numeric entry as something is entered in any of the F2:Q2 cells with a sheet change event. e.g.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    Re: How to adress any text and automatically exit the code?

    Thanks again for answering.

    Yes, I ment message box and not text box, you are 100% correct

    So, I have now tried different solutions, including your last one, and I have found something weird.
    I ended up using this:

    Please Login or Register  to view this content.
    And I noticed when I wrote only one cell in the range: E.G: Range("F2") it worked. It also did that for all the other cells when I just kept one cell in the range. But when I did "Range("F2:M2") it did not work anymore even though all the cells worked when I had them one by one in the range.

    any idea why this is the case?

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to adress any text and automatically exit the code?

    The ISNUMERIC tests for a value in a single cell. If you specify a number of cells it will assume that it's the first one that you mean, i.e. F2.
    How could it be otherwise? If there was to be some strange version of ISNUMERIC that permitted a range of cells, what should it return if there was a mixture of alpha and numeric.

    I believe your optimal solution is to use the Worksheet Change event to trap a number as it is entered, then you never get any non numeric characters and you don't need to retrospectively check a series of entries in a macro.

  11. #11
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    74

    Re: How to adress any text and automatically exit the code?

    Thanks!

    I used your previous code, but made a small change.
    Below you can see your code, and below that code again you can see the change I made. Your code changed the cell value to "" no matter if it was a number or text in the cell. The new code (below yours) changes the cell value only if it is something else than a number in it.

    Thank you again!

    Please Login or Register  to view this content.

    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: 16
    Last Post: 11-07-2019, 12:15 PM
  2. code to automatically convert cell text to comments
    By tabford in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2017, 06:46 PM
  3. Identify code invoked on cell exit / enter - Finde Invoked Code on Cell Exit
    By vhache in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2017, 05:19 PM
  4. Macro code to automatically save and exit after 2 minutes!
    By Garth2014 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2014, 11:03 PM
  5. Changing text of Target.Adress using replace in VBA
    By claudyio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2014, 09:17 AM
  6. Trim code to exit code if cell is empty
    By excellenthelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2013, 12:08 PM
  7. [SOLVED] Formula to extract text before the first dot in an E-mail adress
    By Rambo4711 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2013, 05:18 AM

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