+ Reply to Thread
Results 1 to 7 of 7

InStr function doesn't find specific text, but finds any text in column and runs code

  1. #1
    Forum Contributor
    Join Date
    11-01-2012
    Location
    NJ
    MS-Off Ver
    Excel 365
    Posts
    109

    InStr function doesn't find specific text, but finds any text in column and runs code

    I have the below code:

    Goal - I want the code to search Column "E" for the word "ExchangeIssue". If text "ExchangeIssue" is not found in Column E then Exit Sub.
    If text "ExchangeIssue" is found, display message box asking to if they want to confirm exchanges. Depending on if answered yes or
    no, "Call Confirm" or "Call No_Confirm" respectively.

    Issues - Line "If InStr(1, Range("E2:E" & lrow).Text, "ExchangeIssue", 1) = 0 Then"
    If there is any word in column E the message box will be displayed, NOT only when the word "ExchangeIssue" exists. If I clear all
    contents in Column E, then the code will exit sub.

    What I tried - I tried changing line to If InStr(1, Range("E2:E" & lrow).Value, "ExchangeIssue", 1) = 0 Then
    but that only return a Run Time Error '13 - Type Mismatch. I think it has something to do with the range, but I need some
    guidance

    Code
    Sub Exchange_Msg()

    Dim lrow As Long

    Sheets("Exchanges").Select

    'find the last row
    lrow = Range("E65536").End(xlUp).row

    'select from E2 to the last used row
    Range("E2:E" & lrow).Select

    If InStr(1, Range("E2:E" & lrow).Text, "ExchangeIssue", 1) = 0 Then
    Exit Sub
    Else

    MSG = MsgBox("Do you want to confirm exchanges?", vbYesNo, "Confirm Exchanges?")
    If MSG = vbYes Then
    Call Confirm
    Else
    Call No_Confirm
    End If
    End If

    End Sub
    End Code

    Any assistance is greatly appreciated!
    Thank you

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    Milkey,
    Please wrap up you code with code tags as per forum's rule.
    I do not think the inStr function works with more than a single range, you have to test it for each single cell.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    Try using Find.
    If posting code please use code tags, see here.

  4. #4
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    Alternative code. Not as simple as AB33's though. I don't know why but I have to do things the hard way lol
    It doesn't matter about Case, the code converts everything to lower case for comparison
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    @ Norie.. I tried using Find with Instr as the find what & couldn't get it to work. I don't know if ExchangeIssue is on it's own or mixed with other text in the cell. If you know how to do it, can I see

  6. #6
    Forum Contributor
    Join Date
    11-01-2012
    Location
    NJ
    MS-Off Ver
    Excel 365
    Posts
    109

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    Thanks guys! Appreciate the help! Think I am all set. I ended up going with Phils, but again thanks to everyone.

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: InStr function doesn't find specific text, but finds any text in column and runs code

    All good Mikey. Here's a Find version for you to play with. I figured it out. You don't use InStr with Find. Find does it better & with no loop
    Cheers
    Phil
    Please Login or Register  to view this content.
    Last edited by Philb1; 03-27-2014 at 03:56 AM. Reason: Hit the wrong key on the keyboard & it all vanished before my eyes

+ 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. VBA Code to Find Specified text within a Column and perform Text to Column
    By MHamid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2014, 06:42 AM
  2. Replies: 0
    Last Post: 04-22-2013, 12:13 PM
  3. Need a macro that finds specific text in all sheets of a workbook
    By aerodragana in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-12-2013, 10:34 AM
  4. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  5. Find function doesn't work with reference and non-visible text in cell (Excel2007)
    By Wiggert in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-25-2009, 09:12 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