excel - VBA to find a word in a range and copy all cells beginning with the same word

Question

I need help with my routine. I have a row (2) in which there is a number of populated cells. At one instance I have a couple (the number changes every now and again) cells consecutively starting with the word Blue (and ending with a number 1, 2 ,3 4 etc.). Ignoring the numbers, I would like to copy all cells (as a range?) starting with the word Blue*. I have managed to get one cell find and copy with the below code:

Sub findcopy()
  Dim rFound As Range
  
  Set rFound = Sheets("page 1").Rows(2).Find(What:="Blue", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If Not rFound Is Nothing Then rFound.Offset(0, 0).Resize(1).Copy Destination:=Sheets("page 1").Range("AG2")

End Sub

Thanks

question from:https://stackoverflow.com/questions/65559696/vba-to-find-a-word-in-a-range-and-copy-all-cells-beginning-with-the-same-word

Answer 1

I guess this should do the job.I will try to refine it a bit more but at the moment this is working. Still, I will welcome other answers (and accept if better) and suggestions before I accept my own. Thanks

Sub SearchX()
Dim c, destination As Range, i As Long
Const SEARCH_TERM As String = "Blue"
Set destination = ActiveSheet.Range("AA10")
For Each c In ActiveSheet.Range("B2:BB2")
i = 1
Do While InStr(i, c.Value, SEARCH_TERM) > 0
destination.Value = c.Value
Set destination = destination.Offset(1, 0)
i = InStr(i, c.Value, SEARCH_TERM) + Len(SEARCH_TERM)
Loop
Next
End Sub