Hi
I want to find one word in a cell that may or maynot be in the cell and then put that word in a cell in the same row.
I found the following code which finds the word (in this case "late") so i have the code for this but unfortunately it just dumps the work "late" into row 2 of the selected column and then in each subsequent row.
Does someone have the code to ensure the work is placed in the same row as the cell with the word "Late" in the text string.
The text string can be any legnth and as i said the word "late" may or not be in the string.
Also it is unlikely that the word would be capitilised, but for completeness sake is there code to search for both "late" and "Late"? This is not critical but it would be good to check for both.
I do not want to find later or latest so should I put a space after late to exclude anything else?
I want to find one word in a cell that may or maynot be in the cell and then put that word in a cell in the same row.
I found the following code which finds the word (in this case "late") so i have the code for this but unfortunately it just dumps the work "late" into row 2 of the selected column and then in each subsequent row.
Does someone have the code to ensure the work is placed in the same row as the cell with the word "Late" in the text string.
The text string can be any legnth and as i said the word "late" may or not be in the string.
Also it is unlikely that the word would be capitilised, but for completeness sake is there code to search for both "late" and "Late"? This is not critical but it would be good to check for both.
I do not want to find later or latest so should I put a space after late to exclude anything else?
VBA Code:
Sub Late()
Dim arry As Variant
Dim Str As Variant
Dim RE As New RegExp
arry = Range("F2:F20000").Value
RE.Pattern = "late"
RE.Global = True
Dim matches As MatchCollection
i = 2
For Each Str In arry
Set matches = RE.Execute(Str)
'Debug.Print RE.Test(Str)
If RE.Test(Str) = True Then
Cells(i, 25) = matches(0)
i = i + 1
End If
Next Str
End Sub
Last edited by a moderator:
