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
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
Try:
If myString like "*A*" Then
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
Source: Stackoverflow.com