CODEWITHSUNDEEP
vbaexcel
JahKnows
JahKnows

Reputation: 2706

VBA, if a string contains a certain letter

I do not usually work with VBA and I cannot figure this out. I am trying to determine whether a certain letter is contained within a string on my spreadhseet.

Private Sub CommandButton1_Click()
Dim myString As String
RowCount = WorksheetFunction.CountA(Range("A:A"))
MsgBox RowCount
For i = 2 To RowCount
    myString = Trim(Cells(i, 1).Value)
    If myString.Contains("A") Then
        oldStr = Cells(i, 15).Value
        newStr = Left(oldStr, oldStr.IndexOf("A"))
    End If
Next          
End Sub

This code should go through a list of values and if it encounters the letter A to remove it and everything that comes after it. I am getting problems at my IF statement, Invalid Qualifier. How would I be able to make my IF statement output whether or not the String in the cell contains the letter A?

Thank you very much

Upvotes: 16

Views: 299211

Answers (4)

Mark Balhoff
Mark Balhoff

Reputation: 2356

Try using the InStr function which returns the index in the string at which the character was found. If InStr returns 0, the string was not found.

If InStr(myString, "A") > 0 Then

InStr MSDN Website

For the error on the line assigning to newStr, convert oldStr.IndexOf to that InStr function also.

Left(oldStr, InStr(oldStr, "A"))

Upvotes: 31

user3810910
user3810910

Reputation: 13

If you are looping through a lot of cells, use the binary function, it is much faster. Using "<> 0" in place of "> 0" also makes it faster:

If InStrB(1, myString, "a", vbBinaryCompare) <> 0

Upvotes: 1

sous2817
sous2817

Reputation: 3960

Not sure if this is what you're after, but it will loop through the range that you gave it and if it finds an "A" it will remove it from the cell. I'm not sure what oldStr is used for...

Private Sub foo()
Dim myString As String
RowCount = WorksheetFunction.CountA(Range("A:A"))

For i = 2 To RowCount
    myString = Trim(Cells(i, 1).Value)
    If InStr(myString, "A") > 0 Then
        Cells(i, 1).Value = Left(myString, InStr(myString, "A"))
    End If
Next
End Sub

Upvotes: 1

PowerUser
PowerUser

Reputation: 11791

Try:

If myString like "*A*" Then

Upvotes: 1

Related Questions