VBA, if a string contains a certain letter

Question

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

Accepted Answer

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"))
```

VBA, if a string contains a certain letter

Solution

Problem