Results 1 to 4 of 4

Thread: VBA Help - If Cell Contains Partial String Then.......
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    554
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Help - If Cell Contains Partial String Then.......

    Hello all,

    Have a question that I thought I could easily answer but for whatever reason it doesn't seem to be working so another pair of eyes may help.

    I have a For Each Cell in Range Statement that looks at a Doc Type (2 Digit Variable) and when it finds "KR" to Search a Offset Cell for Partial Text, "P Card" if the code finds it to drop in the Text "P Card" in another cell.

    Currently when I run the code it completely bypasses the cells with "P Card" and seems like it doesn't see it. When I modify the line to search for only "P" the code seems to work but also tags things that start with "P" that don't say "P Card". I have commented the line that is causing the issue.

    An example of a P Card text: "P CARD 20190622" - This is what the code needs to flag with the "P Card" Text
    A non P Card but Starts with "P" Text: "P Statement"


    Code:
    Sub PopulateNonPO()
    
    
    Dim ws1             As Worksheet, ws2 As Worksheet
    Dim LastR           As Long, LastR2 As Long
    Dim Cell            As Range
    Dim DocType     As String
    
    
    Set ws1 = Sheets("M_T_KSB1")
    
    
    LastR = ws1.Cells(Rows.Count, "C").End(xlUp).Row  '2 refers to the row to start on
    
    
        For Each Cell In ws1.Range("J2:J" & LastR)
            DocType = Cell.Value
        
                Select Case DocType
                Case "YA"
                    Cell.Offset(0, 19).Value = "Accrual" 'Updates PO Field
                Case "SA"
                    Cell.Offset(0, 19).Value = "JVWF"   'Updates PO Field
                    Cell.Offset(0, 21).Value = Cell.Offset(0, 15).Value 'Drops in "Name" into Vendor Field
                Case "ZO"
                    Cell.Offset(0, 19).Value = "T&E"    'Updates PO Field
                 Case "KR"
                    If InStr(1, Cell.Offset(0, 2), "*P Card*") > 0 Then   'Issue is here <------------------
                        Cell.Offset(0, 19).Value = "P Card"
                    Else
                        Cell.Offset(0, 19).Value = "Pay Req"
                    End If
                Case "KG"
                    If InStr(1, Cell.Offset(0, 2), "P Card") Then
                        Cell.Offset(0, 19).Value = "P Card"
                    Else
                        Cell.Offset(0, 19).Value = "Pay Req"
                    End If
            End Select
        Next Cell
     
    End Sub
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Help - If Cell Contains Partial String Then.......

    try without wildcard

    Code:
    If InStr(1, Cell.Offset(0, 2), "P Card) > 0 Then
    Regards Dante Amor

  3. #3
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    554
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Help - If Cell Contains Partial String Then.......

    Just curious, was this intentionally written with only a single " ? I would imagine this to need " "
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Help - If Cell Contains Partial String Then.......

    sorry, typo error

    Code:
    If InStr(1, Cell.Offset(0, 2), "P Card") > 0 Then
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •