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

User · Answer

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    gt  0 Then         Cells i  1  Value   Left myString  InStr myString   A        End If Next End Sub

User · 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    gt  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

User · Answer

If you are looping through a lot of cells  use the binary function  it is much faster  Using   lt   0  in place of    0  also makes it faster   If InStrB 1  myString   a   vbBinaryCompare   lt  gt  0

User · Answer

Try   If myString like   A   Then

[vba] VBA, if a string contains a certain letter

Examples related to vba

Examples related to excel