0

I am trying to search a column for cells that contain certain text, and if they do, copy the value next to it and paste in the first free row of an assigned range. Below is the code I am using, it will return the one value but is not looping to return the others.

Const SB As String = "*SB*"
Dim cel As Range
Dim sbcellvalue As Variant
Dim SBpull As Range
Dim SBpaste As Range

Set SBpull = Worksheets("SBImport").Range("Q:Q")
Set SBpaste = Worksheets("Misc").Range("SBRange")

For Each cel In SBpull.Cells

    If InStr(1, sbcellvalue, SB, vbTextCompare) > 0 Then
    
        sbcellvalue = cel.Offset(0, 1).Value
        SBpaste.Offset(1, 0).End(xlUp) = sbcellvalue
        
    End If
Next cel

MsgBox "search done"

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
arisophia
  • 61
  • 4
  • Seems like should be `Instr(1, cel.Value, SB...)` – BigBen Sep 09 '21 at 16:23
  • 1
    Side note: consider [finding the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) in column Q instead of looping all the way to the very very bottom of the sheet. – BigBen Sep 09 '21 at 16:33

0 Answers0