move cell contents based on presence of a string
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: move cell contents based on presence of a string

  1. #1
    Guest

    Default

     
    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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
    _________________

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com