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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
572
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,733
Office Version
2007
Platform
Windows
try without wildcard

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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
572
Just curious, was this intentionally written with only a single " ? I would imagine this to need " "
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,733
Office Version
2007
Platform
Windows
sorry, typo error

Code:
If InStr(1, Cell.Offset(0, 2), [B][COLOR=#ff0000]"[/COLOR][/B]P Card[COLOR=#ff0000][B]"[/B][/COLOR]) > 0 Then
 

Forum statistics

Threads
1,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top