move cell contents based on presence of a string

[Guest]

Hi all
Long time lurker, first time poster! Fantastic archive of info on this site but I couldn't quite find enough of an answer to solve my problem. Here it is: 3 columns (a,b, and c) and lots of rows. I need to move down column b searching for a string that ALWAYS starts with text = "dba/". When those characters are found I would like to grab the contents of that cell in column b as well as the contents of the adjacent cell in column c and MOVE them both one cell to the left. So column b contents end up being moved into column a AND column c contents end up being moved to column b. And then resume searching down column b for the next occurence of "dba/". I can use FIND to find "dba/" but I cannot figure out the loop and automated copy of the two cells into their final locations.
many thanks
JB

 

[Dave Hawley]

Hi JB


Try

Sub FindIt()
Dim i As Integer
Dim iRow As Integer

iRow = 1
For i = 1 To WorksheetFunction.CountIf(Columns(2), "J**")

iRow = Columns(2).Find(What:="DBA/", After:=Cells(iRow, 2), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Row

Cells(iRow, 2).Range("A1:B1").Copy Destination:=Cells(iRow, 2).Offset(1, -1)

Next i
End Sub

 

[Dave Hawley]

Oops, yet another typo


Sub FindIt()
Dim i As Integer
Dim iRow As Integer

iRow = 1
For i = 1 To WorksheetFunction.CountIf(Columns(2), "DBA/")

iRow = Columns(2).Find(What:="DBA/", After:=Cells(iRow, 2), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Row

Cells(iRow, 2).Range("A1:B1").Copy Destination:=Cells(iRow, 2).Offset(1, -1)

Next i
End Sub
_________________