+ Reply to Thread
Results 1 to 5 of 5

Search String and mark wrong

  1. 05-15-2009, 09:40 AM #1
    StryderLlama
    StryderLlama is offline
    Registered User
    Join Date
    11-07-2007
    Location
    Louisville
    Posts
    9

    Search String and mark wrong

    I have an audit file that marks certain criteria wrong.

    I need to make this statement happen:
    If Column G contains "$" then Column G must also contain "A","D","N", and "6".

    If A, D, N, or 6 anyone of those is missing, I need to mark the cell wrong. Here is what I have been trying to do, but it is not working, any help would be much appreciated.


    Dim SearchString, SearchChar, MyPos, SearchCharA, SearchCharD, SearchCharN, SearchChar6, MyPosA, MyPosD, MyPosN, MyPos6, MyTotalPos
    SearchString = Range("G" & x) ' String to search in.
    SearchChar = "$" ' Search for "$".
    SearchCharA = "A"
    SearchCharD = "D"
    SearchCharN = "N"
    SearchChar6 = "6"

    MyPos = InStr(SearchString, SearchChar)
    MyPosA = InStr(SearchString, SearchCharA)
    MyPosD = InStr(SearchString, SearchCharD)
    MyPosN = InStr(SearchString, SearchCharN)
    MyPos6 = InStr(SearchString, SearchChar6)

    If MyPos > 0 Then
    If MyPosA <> 0 Then
    If MyPosD <> 0 Then
    If MyPosN <> 0 Then
    If MyPos6 = 0 Then
    FieldValue = False
    Range("G" & x).Font.Bold = True
    Range("G" & x).EntireRow.Interior.ColorIndex = 20
    Range("A" & x).Cells.Value = "x"
    Range("G" & x).AddComment
    Range("G" & x).Comment.Visible = False
    Range("G" & x).Comment.Text Text:="PLQA:" & Chr(10) & "Missing POT 6"
    ' Range("O" & x).Interior.ColorIndex = 6
    End If
    Else
    FieldValue = False
    Range("G" & x).Font.Bold = True
    Range("G" & x).EntireRow.Interior.ColorIndex = 20
    Range("A" & x).Cells.Value = "x"
    Range("G" & x).AddComment
    Range("G" & x).Comment.Visible = False
    Range("G" & x).Comment.Text Text:="PLQA:" & Chr(10) & "Missing POT N"
    ' Range("O" & x).Interior.ColorIndex = 6
    End If
    Else
    FieldValue = False
    Range("G" & x).Font.Bold = True
    Range("G" & x).EntireRow.Interior.ColorIndex = 20
    Range("A" & x).Cells.Value = "x"
    Range("G" & x).AddComment
    Range("G" & x).Comment.Visible = False
    Range("G" & x).Comment.Text Text:="PLQA:" & Chr(10) & "Missing POT D"
    ' Range("O" & x).Interior.ColorIndex = 6
    End If
    Else
    FieldValue = False
    Range("G" & x).Font.Bold = True
    Range("G" & x).EntireRow.Interior.ColorIndex = 20
    Range("A" & x).Cells.Value = "x"
    Range("G" & x).AddComment
    Range("G" & x).Comment.Visible = False
    Range("G" & x).Comment.Text Text:="PLQA:" & Chr(10) & "Missing POT A"
    ' Range("O" & x).Interior.ColorIndex = 6
    End If
    Else
    FieldValue = True
    End If
    Reply With QuoteRegister To Reply
  2. 05-15-2009, 09:51 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Search String and mark wrong

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 05-15-2009, 09:54 AM #3
    stanleydgromjr
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Search String and mark wrong

    StryderLlama,

    Can you post your workbook (or a sample of) containing your macro - scroll down and see "Manage Attachments".
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  4. 05-15-2009, 10:07 AM #4
    StryderLlama
    StryderLlama is offline
    Registered User
    Join Date
    11-07-2007
    Location
    Louisville
    Posts
    9

    Re: Search String and mark wrong 

    Please Login or Register  to view this content.
    Last edited by StryderLlama; 05-15-2009 at 10:10 AM.
    Reply With QuoteRegister To Reply
  5. 05-15-2009, 04:10 PM #5
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Search String and mark wrong

    Hello StryderLlama,

    This macro uses Regular Expressions pattern matching to make the code shorter and faster. This macro could probably be speed up if I knew what the expected cell contents looked like.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    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