+ Reply to Thread
Results 1 to 11 of 11

creating macro VB in Excel - find and move program

  1. #1
    DictatorDraco
    Guest

    creating macro VB in Excel - find and move program

    i've never touched visual basic before, but i managed to take the source from
    FindItAll (though i don't think it was the actual source seeing as how it
    didn't work) and edit it to fit my needs.

    looking to make a VB macro in Excel that will find a cell and move the
    entire row that cell is in to the top. doing this for work, and my boss knows
    i'm not a programmer. i think he wants me to learn. if anyone could point out
    bugs or tell me commands, it would be much appreciated.

    here's the code:

    Sub FindAndMoveToTop()
    Dim FirstCell As Range
    Dim NextCell As Range
    Dim WhatToFind As Variant
    'Window prompt allowing user to define WhatToFind
    WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    100, 100, , , 2)
    'If WhatToFind is a value and not blank, move on
    If WhatToFind <> "" And Not WhatToFind = False Then
    'Start with first worksheet
    Worksheets("Sheet1").Activate
    'Start at first cell
    Range("A1").Select
    'Find the first cell containing WhatToFind (specified by user)
    Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    MatchCase:=False)
    'If FirstCell exists, move on
    If Not FirstCell Is Nothing Then
    'Ok, First Cell is set
    FirstCell.Activate
    'Keep going
    On Error Resume Next
    'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    While (Not NextCell Is Nothing) And (Not NextCell.Address = FirstCell.Address)
    'Find next row containing what is in FirstCell (ActiveCell) and define as
    NextCell
    Set NextCell = Cells.FindNext(After:=ActiveCell)
    'Don't mistake NextCell for FirstCell, move on
    If Not NextCell.Address = FirstCell.Address Then
    'Activate subsequent NextCells
    NextCell.Activate
    End If
    Wend
    End If
    'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    selecting the cell, not the row.
    'Also, if >1 instance of WhatToFind, acts funky...
    Worksheets("Sheet1").Rows(ActiveCell).Select
    'Cut all rows containing WhatToFind
    Selection.Cut
    'Back to A1
    Cells(1, 1).Select
    'Insert cut rows here
    Selection.Insert Shift:=xlDown
    'Clean up
    Set NextCell = Nothing
    Set FirstCell = Nothing
    Range("A1").Select
    End If
    End Sub


    major problem is how to select ALL of the ROWS that WhatToFind is found in.
    also, if WhatToFind is found in two cells in the same row, it will move the
    second cell containing it in that row to the next unused row. idk. it acts
    really funky.

    i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    spot.
    i think Cells(1, 1).Select might be causing the odd behavior for two cells
    in the same row.

    any ideas?

  2. #2
    Registered User
    Join Date
    08-18-2004
    Posts
    16
    Try...

    Worksheets("Sheet1").Rows(ActiveCell.Row).EntireRow.Select

  3. #3
    Don Guillett
    Guest

    Re: creating macro VB in Excel - find and move program

    Try another approach by using
    data>filter>autofilter>copy>paste
    Record that and modify to suit
    I did something like this for a client yesterday.

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    > i've never touched visual basic before, but i managed to take the source
    > from
    > FindItAll (though i don't think it was the actual source seeing as how it
    > didn't work) and edit it to fit my needs.
    >
    > looking to make a VB macro in Excel that will find a cell and move the
    > entire row that cell is in to the top. doing this for work, and my boss
    > knows
    > i'm not a programmer. i think he wants me to learn. if anyone could point
    > out
    > bugs or tell me commands, it would be much appreciated.
    >
    > here's the code:
    >
    > Sub FindAndMoveToTop()
    > Dim FirstCell As Range
    > Dim NextCell As Range
    > Dim WhatToFind As Variant
    > 'Window prompt allowing user to define WhatToFind
    > WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    > 100, 100, , , 2)
    > 'If WhatToFind is a value and not blank, move on
    > If WhatToFind <> "" And Not WhatToFind = False Then
    > 'Start with first worksheet
    > Worksheets("Sheet1").Activate
    > 'Start at first cell
    > Range("A1").Select
    > 'Find the first cell containing WhatToFind (specified by user)
    > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > MatchCase:=False)
    > 'If FirstCell exists, move on
    > If Not FirstCell Is Nothing Then
    > 'Ok, First Cell is set
    > FirstCell.Activate
    > 'Keep going
    > On Error Resume Next
    > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > While (Not NextCell Is Nothing) And (Not NextCell.Address =
    > FirstCell.Address)
    > 'Find next row containing what is in FirstCell (ActiveCell) and define as
    > NextCell
    > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > 'Don't mistake NextCell for FirstCell, move on
    > If Not NextCell.Address = FirstCell.Address Then
    > 'Activate subsequent NextCells
    > NextCell.Activate
    > End If
    > Wend
    > End If
    > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    > selecting the cell, not the row.
    > 'Also, if >1 instance of WhatToFind, acts funky...
    > Worksheets("Sheet1").Rows(ActiveCell).Select
    > 'Cut all rows containing WhatToFind
    > Selection.Cut
    > 'Back to A1
    > Cells(1, 1).Select
    > 'Insert cut rows here
    > Selection.Insert Shift:=xlDown
    > 'Clean up
    > Set NextCell = Nothing
    > Set FirstCell = Nothing
    > Range("A1").Select
    > End If
    > End Sub
    >
    >
    > major problem is how to select ALL of the ROWS that WhatToFind is found
    > in.
    > also, if WhatToFind is found in two cells in the same row, it will move
    > the
    > second cell containing it in that row to the next unused row. idk. it acts
    > really funky.
    >
    > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    > spot.
    > i think Cells(1, 1).Select might be causing the odd behavior for two cells
    > in the same row.
    >
    > any ideas?




  4. #4
    Bob Phillips
    Guest

    Re: creating macro VB in Excel - find and move program

    Sub FindAndMoveToTop()
    Dim FirstCell As Range
    Dim NextCell As Range
    Dim WhatToFind As Variant
    Dim TargetCells As Range

    'Window prompt allowing user to define WhatToFind
    WhatToFind = Application.InputBox("What are you looking for?", _
    "Search", , 100, 100, , , 2)

    'If WhatToFind is a value and not blank, move on
    If WhatToFind <> "" And Not WhatToFind = False Then

    'Start with first worksheet
    Worksheets("Sheet1").Activate

    'Find the first cell containing WhatToFind (specified by user)
    Set NextCell = Cells.Find(What:=WhatToFind, _
    after:=Range("A1"), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

    'If FirstCell exists, move on
    If Not NextCell Is Nothing Then
    'Ok, First Cell is set
    Set TargetCells = NextCell

    'Keep going
    On Error Resume Next

    Set FirstCell = NextCell

    Do
    Set NextCell = Cells.FindNext(NextCell)

    If Not NextCell Is Nothing Then
    Set TargetCells = Union(TargetCells, NextCell)
    End If
    Loop While Not NextCell Is Nothing And _
    NextCell.Address <> FirstCell.Address

    End If

    TargetCells.EntireRow.Select
    Selection.Cut
    'Back to A1
    Cells(1, 1).Select
    'Insert cut rows here
    Selection.Insert Shift:=xlDown
    'Clean up
    Set TargetCells = Nothing
    Set NextCell = Nothing
    Set FirstCell = Nothing
    Range("A1").Select
    End If
    End Sub


    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    > i've never touched visual basic before, but i managed to take the source

    from
    > FindItAll (though i don't think it was the actual source seeing as how it
    > didn't work) and edit it to fit my needs.
    >
    > looking to make a VB macro in Excel that will find a cell and move the
    > entire row that cell is in to the top. doing this for work, and my boss

    knows
    > i'm not a programmer. i think he wants me to learn. if anyone could point

    out
    > bugs or tell me commands, it would be much appreciated.
    >
    > here's the code:
    >
    > Sub FindAndMoveToTop()
    > Dim FirstCell As Range
    > Dim NextCell As Range
    > Dim WhatToFind As Variant
    > 'Window prompt allowing user to define WhatToFind
    > WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    > 100, 100, , , 2)
    > 'If WhatToFind is a value and not blank, move on
    > If WhatToFind <> "" And Not WhatToFind = False Then
    > 'Start with first worksheet
    > Worksheets("Sheet1").Activate
    > 'Start at first cell
    > Range("A1").Select
    > 'Find the first cell containing WhatToFind (specified by user)
    > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > MatchCase:=False)
    > 'If FirstCell exists, move on
    > If Not FirstCell Is Nothing Then
    > 'Ok, First Cell is set
    > FirstCell.Activate
    > 'Keep going
    > On Error Resume Next
    > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > While (Not NextCell Is Nothing) And (Not NextCell.Address =

    FirstCell.Address)
    > 'Find next row containing what is in FirstCell (ActiveCell) and define as
    > NextCell
    > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > 'Don't mistake NextCell for FirstCell, move on
    > If Not NextCell.Address = FirstCell.Address Then
    > 'Activate subsequent NextCells
    > NextCell.Activate
    > End If
    > Wend
    > End If
    > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    > selecting the cell, not the row.
    > 'Also, if >1 instance of WhatToFind, acts funky...
    > Worksheets("Sheet1").Rows(ActiveCell).Select
    > 'Cut all rows containing WhatToFind
    > Selection.Cut
    > 'Back to A1
    > Cells(1, 1).Select
    > 'Insert cut rows here
    > Selection.Insert Shift:=xlDown
    > 'Clean up
    > Set NextCell = Nothing
    > Set FirstCell = Nothing
    > Range("A1").Select
    > End If
    > End Sub
    >
    >
    > major problem is how to select ALL of the ROWS that WhatToFind is found

    in.
    > also, if WhatToFind is found in two cells in the same row, it will move

    the
    > second cell containing it in that row to the next unused row. idk. it acts
    > really funky.
    >
    > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    > spot.
    > i think Cells(1, 1).Select might be causing the odd behavior for two cells
    > in the same row.
    >
    > any ideas?




  5. #5
    DictatorDraco
    Guest

    Re: creating macro VB in Excel - find and move program

    I tried brassman's approach since it was simplest. It worked! Thanks to all
    of you.

    "Brassman" wrote:

    >
    > Try...
    >
    > Worksheets("Sheet1").Rows(ActiveCell.Row).EntireRow.Select
    >
    >
    > --
    > Brassman
    > ------------------------------------------------------------------------
    > Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290
    > View this thread: http://www.excelforum.com/showthread...hreadid=547421
    >
    >


  6. #6
    DictatorDraco
    Guest

    Re: creating macro VB in Excel - find and move program

    Uhhh... didn't quite work. Almost.
    It acts funky again if there is more than one row containing WhatToFind

    "DictatorDraco" wrote:

    > I tried brassman's approach since it was simplest. It worked! Thanks to all
    > of you.
    >
    > "Brassman" wrote:
    >
    > >
    > > Try...
    > >
    > > Worksheets("Sheet1").Rows(ActiveCell.Row).EntireRow.Select
    > >
    > >
    > > --
    > > Brassman
    > > ------------------------------------------------------------------------
    > > Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290
    > > View this thread: http://www.excelforum.com/showthread...hreadid=547421
    > >
    > >


  7. #7
    DictatorDraco
    Guest

    Re: creating macro VB in Excel - find and move program

    Didn't work :-( Thanks anyway

    "Bob Phillips" wrote:

    > Sub FindAndMoveToTop()
    > Dim FirstCell As Range
    > Dim NextCell As Range
    > Dim WhatToFind As Variant
    > Dim TargetCells As Range
    >
    > 'Window prompt allowing user to define WhatToFind
    > WhatToFind = Application.InputBox("What are you looking for?", _
    > "Search", , 100, 100, , , 2)
    >
    > 'If WhatToFind is a value and not blank, move on
    > If WhatToFind <> "" And Not WhatToFind = False Then
    >
    > 'Start with first worksheet
    > Worksheets("Sheet1").Activate
    >
    > 'Find the first cell containing WhatToFind (specified by user)
    > Set NextCell = Cells.Find(What:=WhatToFind, _
    > after:=Range("A1"), _
    > LookIn:=xlValues, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > SearchDirection:=xlNext, _
    > MatchCase:=False)
    >
    > 'If FirstCell exists, move on
    > If Not NextCell Is Nothing Then
    > 'Ok, First Cell is set
    > Set TargetCells = NextCell
    >
    > 'Keep going
    > On Error Resume Next
    >
    > Set FirstCell = NextCell
    >
    > Do
    > Set NextCell = Cells.FindNext(NextCell)
    >
    > If Not NextCell Is Nothing Then
    > Set TargetCells = Union(TargetCells, NextCell)
    > End If
    > Loop While Not NextCell Is Nothing And _
    > NextCell.Address <> FirstCell.Address
    >
    > End If
    >
    > TargetCells.EntireRow.Select
    > Selection.Cut
    > 'Back to A1
    > Cells(1, 1).Select
    > 'Insert cut rows here
    > Selection.Insert Shift:=xlDown
    > 'Clean up
    > Set TargetCells = Nothing
    > Set NextCell = Nothing
    > Set FirstCell = Nothing
    > Range("A1").Select
    > End If
    > End Sub
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    > news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    > > i've never touched visual basic before, but i managed to take the source

    > from
    > > FindItAll (though i don't think it was the actual source seeing as how it
    > > didn't work) and edit it to fit my needs.
    > >
    > > looking to make a VB macro in Excel that will find a cell and move the
    > > entire row that cell is in to the top. doing this for work, and my boss

    > knows
    > > i'm not a programmer. i think he wants me to learn. if anyone could point

    > out
    > > bugs or tell me commands, it would be much appreciated.
    > >
    > > here's the code:
    > >
    > > Sub FindAndMoveToTop()
    > > Dim FirstCell As Range
    > > Dim NextCell As Range
    > > Dim WhatToFind As Variant
    > > 'Window prompt allowing user to define WhatToFind
    > > WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    > > 100, 100, , , 2)
    > > 'If WhatToFind is a value and not blank, move on
    > > If WhatToFind <> "" And Not WhatToFind = False Then
    > > 'Start with first worksheet
    > > Worksheets("Sheet1").Activate
    > > 'Start at first cell
    > > Range("A1").Select
    > > 'Find the first cell containing WhatToFind (specified by user)
    > > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > > MatchCase:=False)
    > > 'If FirstCell exists, move on
    > > If Not FirstCell Is Nothing Then
    > > 'Ok, First Cell is set
    > > FirstCell.Activate
    > > 'Keep going
    > > On Error Resume Next
    > > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > > While (Not NextCell Is Nothing) And (Not NextCell.Address =

    > FirstCell.Address)
    > > 'Find next row containing what is in FirstCell (ActiveCell) and define as
    > > NextCell
    > > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > > 'Don't mistake NextCell for FirstCell, move on
    > > If Not NextCell.Address = FirstCell.Address Then
    > > 'Activate subsequent NextCells
    > > NextCell.Activate
    > > End If
    > > Wend
    > > End If
    > > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    > > selecting the cell, not the row.
    > > 'Also, if >1 instance of WhatToFind, acts funky...
    > > Worksheets("Sheet1").Rows(ActiveCell).Select
    > > 'Cut all rows containing WhatToFind
    > > Selection.Cut
    > > 'Back to A1
    > > Cells(1, 1).Select
    > > 'Insert cut rows here
    > > Selection.Insert Shift:=xlDown
    > > 'Clean up
    > > Set NextCell = Nothing
    > > Set FirstCell = Nothing
    > > Range("A1").Select
    > > End If
    > > End Sub
    > >
    > >
    > > major problem is how to select ALL of the ROWS that WhatToFind is found

    > in.
    > > also, if WhatToFind is found in two cells in the same row, it will move

    > the
    > > second cell containing it in that row to the next unused row. idk. it acts
    > > really funky.
    > >
    > > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    > > spot.
    > > i think Cells(1, 1).Select might be causing the odd behavior for two cells
    > > in the same row.
    > >
    > > any ideas?

    >
    >
    >


  8. #8
    DictatorDraco
    Guest

    Re: creating macro VB in Excel - find and move program

    No luck. Thanks though.

    "Don Guillett" wrote:

    > Try another approach by using
    > data>filter>autofilter>copy>paste
    > Record that and modify to suit
    > I did something like this for a client yesterday.
    >
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    > news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    > > i've never touched visual basic before, but i managed to take the source
    > > from
    > > FindItAll (though i don't think it was the actual source seeing as how it
    > > didn't work) and edit it to fit my needs.
    > >
    > > looking to make a VB macro in Excel that will find a cell and move the
    > > entire row that cell is in to the top. doing this for work, and my boss
    > > knows
    > > i'm not a programmer. i think he wants me to learn. if anyone could point
    > > out
    > > bugs or tell me commands, it would be much appreciated.
    > >
    > > here's the code:
    > >
    > > Sub FindAndMoveToTop()
    > > Dim FirstCell As Range
    > > Dim NextCell As Range
    > > Dim WhatToFind As Variant
    > > 'Window prompt allowing user to define WhatToFind
    > > WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    > > 100, 100, , , 2)
    > > 'If WhatToFind is a value and not blank, move on
    > > If WhatToFind <> "" And Not WhatToFind = False Then
    > > 'Start with first worksheet
    > > Worksheets("Sheet1").Activate
    > > 'Start at first cell
    > > Range("A1").Select
    > > 'Find the first cell containing WhatToFind (specified by user)
    > > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > > MatchCase:=False)
    > > 'If FirstCell exists, move on
    > > If Not FirstCell Is Nothing Then
    > > 'Ok, First Cell is set
    > > FirstCell.Activate
    > > 'Keep going
    > > On Error Resume Next
    > > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > > While (Not NextCell Is Nothing) And (Not NextCell.Address =
    > > FirstCell.Address)
    > > 'Find next row containing what is in FirstCell (ActiveCell) and define as
    > > NextCell
    > > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > > 'Don't mistake NextCell for FirstCell, move on
    > > If Not NextCell.Address = FirstCell.Address Then
    > > 'Activate subsequent NextCells
    > > NextCell.Activate
    > > End If
    > > Wend
    > > End If
    > > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    > > selecting the cell, not the row.
    > > 'Also, if >1 instance of WhatToFind, acts funky...
    > > Worksheets("Sheet1").Rows(ActiveCell).Select
    > > 'Cut all rows containing WhatToFind
    > > Selection.Cut
    > > 'Back to A1
    > > Cells(1, 1).Select
    > > 'Insert cut rows here
    > > Selection.Insert Shift:=xlDown
    > > 'Clean up
    > > Set NextCell = Nothing
    > > Set FirstCell = Nothing
    > > Range("A1").Select
    > > End If
    > > End Sub
    > >
    > >
    > > major problem is how to select ALL of the ROWS that WhatToFind is found
    > > in.
    > > also, if WhatToFind is found in two cells in the same row, it will move
    > > the
    > > second cell containing it in that row to the next unused row. idk. it acts
    > > really funky.
    > >
    > > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    > > spot.
    > > i think Cells(1, 1).Select might be causing the odd behavior for two cells
    > > in the same row.
    > >
    > > any ideas?

    >
    >
    >


  9. #9
    Bob Phillips
    Guest

    Re: creating macro VB in Excel - find and move program

    Did for me.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    news:12FEBB64-F973-4586-A4DF-DA53F9035D43@microsoft.com...
    > Didn't work :-( Thanks anyway
    >
    > "Bob Phillips" wrote:
    >
    > > Sub FindAndMoveToTop()
    > > Dim FirstCell As Range
    > > Dim NextCell As Range
    > > Dim WhatToFind As Variant
    > > Dim TargetCells As Range
    > >
    > > 'Window prompt allowing user to define WhatToFind
    > > WhatToFind = Application.InputBox("What are you looking for?", _
    > > "Search", , 100, 100, , , 2)
    > >
    > > 'If WhatToFind is a value and not blank, move on
    > > If WhatToFind <> "" And Not WhatToFind = False Then
    > >
    > > 'Start with first worksheet
    > > Worksheets("Sheet1").Activate
    > >
    > > 'Find the first cell containing WhatToFind (specified by user)
    > > Set NextCell = Cells.Find(What:=WhatToFind, _
    > > after:=Range("A1"), _
    > > LookIn:=xlValues, _
    > > LookAt:=xlPart, _
    > > SearchOrder:=xlByRows, _
    > > SearchDirection:=xlNext, _
    > > MatchCase:=False)
    > >
    > > 'If FirstCell exists, move on
    > > If Not NextCell Is Nothing Then
    > > 'Ok, First Cell is set
    > > Set TargetCells = NextCell
    > >
    > > 'Keep going
    > > On Error Resume Next
    > >
    > > Set FirstCell = NextCell
    > >
    > > Do
    > > Set NextCell = Cells.FindNext(NextCell)
    > >
    > > If Not NextCell Is Nothing Then
    > > Set TargetCells = Union(TargetCells, NextCell)
    > > End If
    > > Loop While Not NextCell Is Nothing And _
    > > NextCell.Address <> FirstCell.Address
    > >
    > > End If
    > >
    > > TargetCells.EntireRow.Select
    > > Selection.Cut
    > > 'Back to A1
    > > Cells(1, 1).Select
    > > 'Insert cut rows here
    > > Selection.Insert Shift:=xlDown
    > > 'Clean up
    > > Set TargetCells = Nothing
    > > Set NextCell = Nothing
    > > Set FirstCell = Nothing
    > > Range("A1").Select
    > > End If
    > > End Sub
    > >
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in

    message
    > > news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    > > > i've never touched visual basic before, but i managed to take the

    source
    > > from
    > > > FindItAll (though i don't think it was the actual source seeing as how

    it
    > > > didn't work) and edit it to fit my needs.
    > > >
    > > > looking to make a VB macro in Excel that will find a cell and move the
    > > > entire row that cell is in to the top. doing this for work, and my

    boss
    > > knows
    > > > i'm not a programmer. i think he wants me to learn. if anyone could

    point
    > > out
    > > > bugs or tell me commands, it would be much appreciated.
    > > >
    > > > here's the code:
    > > >
    > > > Sub FindAndMoveToTop()
    > > > Dim FirstCell As Range
    > > > Dim NextCell As Range
    > > > Dim WhatToFind As Variant
    > > > 'Window prompt allowing user to define WhatToFind
    > > > WhatToFind = Application.InputBox("What are you looking for?",

    "Search", ,
    > > > 100, 100, , , 2)
    > > > 'If WhatToFind is a value and not blank, move on
    > > > If WhatToFind <> "" And Not WhatToFind = False Then
    > > > 'Start with first worksheet
    > > > Worksheets("Sheet1").Activate
    > > > 'Start at first cell
    > > > Range("A1").Select
    > > > 'Find the first cell containing WhatToFind (specified by user)
    > > > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > > > MatchCase:=False)
    > > > 'If FirstCell exists, move on
    > > > If Not FirstCell Is Nothing Then
    > > > 'Ok, First Cell is set
    > > > FirstCell.Activate
    > > > 'Keep going
    > > > On Error Resume Next
    > > > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > > > While (Not NextCell Is Nothing) And (Not NextCell.Address =

    > > FirstCell.Address)
    > > > 'Find next row containing what is in FirstCell (ActiveCell) and define

    as
    > > > NextCell
    > > > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > > > 'Don't mistake NextCell for FirstCell, move on
    > > > If Not NextCell.Address = FirstCell.Address Then
    > > > 'Activate subsequent NextCells
    > > > NextCell.Activate
    > > > End If
    > > > Wend
    > > > End If
    > > > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!!

    only
    > > > selecting the cell, not the row.
    > > > 'Also, if >1 instance of WhatToFind, acts funky...
    > > > Worksheets("Sheet1").Rows(ActiveCell).Select
    > > > 'Cut all rows containing WhatToFind
    > > > Selection.Cut
    > > > 'Back to A1
    > > > Cells(1, 1).Select
    > > > 'Insert cut rows here
    > > > Selection.Insert Shift:=xlDown
    > > > 'Clean up
    > > > Set NextCell = Nothing
    > > > Set FirstCell = Nothing
    > > > Range("A1").Select
    > > > End If
    > > > End Sub
    > > >
    > > >
    > > > major problem is how to select ALL of the ROWS that WhatToFind is

    found
    > > in.
    > > > also, if WhatToFind is found in two cells in the same row, it will

    move
    > > the
    > > > second cell containing it in that row to the next unused row. idk. it

    acts
    > > > really funky.
    > > >
    > > > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major

    problem
    > > > spot.
    > > > i think Cells(1, 1).Select might be causing the odd behavior for two

    cells
    > > > in the same row.
    > > >
    > > > any ideas?

    > >
    > >
    > >




  10. #10
    Don Guillett
    Guest

    Re: creating macro VB in Excel - find and move program

    You may send me your workbook along with a detailed explanation of what you
    want..

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in message
    news:21FFCA23-5696-4E4B-BDD5-922AC021B4CB@microsoft.com...
    > No luck. Thanks though.
    >
    > "Don Guillett" wrote:
    >
    >> Try another approach by using
    >> data>filter>autofilter>copy>paste
    >> Record that and modify to suit
    >> I did something like this for a client yesterday.
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> dguillett1@austin.rr.com
    >> "DictatorDraco" <DictatorDraco@discussions.microsoft.com> wrote in
    >> message
    >> news:388C18D9-EEC8-4956-A787-B503697CD89D@microsoft.com...
    >> > i've never touched visual basic before, but i managed to take the
    >> > source
    >> > from
    >> > FindItAll (though i don't think it was the actual source seeing as how
    >> > it
    >> > didn't work) and edit it to fit my needs.
    >> >
    >> > looking to make a VB macro in Excel that will find a cell and move the
    >> > entire row that cell is in to the top. doing this for work, and my boss
    >> > knows
    >> > i'm not a programmer. i think he wants me to learn. if anyone could
    >> > point
    >> > out
    >> > bugs or tell me commands, it would be much appreciated.
    >> >
    >> > here's the code:
    >> >
    >> > Sub FindAndMoveToTop()
    >> > Dim FirstCell As Range
    >> > Dim NextCell As Range
    >> > Dim WhatToFind As Variant
    >> > 'Window prompt allowing user to define WhatToFind
    >> > WhatToFind = Application.InputBox("What are you looking for?",
    >> > "Search", ,
    >> > 100, 100, , , 2)
    >> > 'If WhatToFind is a value and not blank, move on
    >> > If WhatToFind <> "" And Not WhatToFind = False Then
    >> > 'Start with first worksheet
    >> > Worksheets("Sheet1").Activate
    >> > 'Start at first cell
    >> > Range("A1").Select
    >> > 'Find the first cell containing WhatToFind (specified by user)
    >> > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    >> > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    >> > MatchCase:=False)
    >> > 'If FirstCell exists, move on
    >> > If Not FirstCell Is Nothing Then
    >> > 'Ok, First Cell is set
    >> > FirstCell.Activate
    >> > 'Keep going
    >> > On Error Resume Next
    >> > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    >> > While (Not NextCell Is Nothing) And (Not NextCell.Address =
    >> > FirstCell.Address)
    >> > 'Find next row containing what is in FirstCell (ActiveCell) and define
    >> > as
    >> > NextCell
    >> > Set NextCell = Cells.FindNext(After:=ActiveCell)
    >> > 'Don't mistake NextCell for FirstCell, move on
    >> > If Not NextCell.Address = FirstCell.Address Then
    >> > 'Activate subsequent NextCells
    >> > NextCell.Activate
    >> > End If
    >> > Wend
    >> > End If
    >> > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    >> > selecting the cell, not the row.
    >> > 'Also, if >1 instance of WhatToFind, acts funky...
    >> > Worksheets("Sheet1").Rows(ActiveCell).Select
    >> > 'Cut all rows containing WhatToFind
    >> > Selection.Cut
    >> > 'Back to A1
    >> > Cells(1, 1).Select
    >> > 'Insert cut rows here
    >> > Selection.Insert Shift:=xlDown
    >> > 'Clean up
    >> > Set NextCell = Nothing
    >> > Set FirstCell = Nothing
    >> > Range("A1").Select
    >> > End If
    >> > End Sub
    >> >
    >> >
    >> > major problem is how to select ALL of the ROWS that WhatToFind is found
    >> > in.
    >> > also, if WhatToFind is found in two cells in the same row, it will move
    >> > the
    >> > second cell containing it in that row to the next unused row. idk. it
    >> > acts
    >> > really funky.
    >> >
    >> > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major
    >> > problem
    >> > spot.
    >> > i think Cells(1, 1).Select might be causing the odd behavior for two
    >> > cells
    >> > in the same row.
    >> >
    >> > any ideas?

    >>
    >>
    >>




  11. #11
    aidan.heritage@virgin.net
    Guest

    Re: creating macro VB in Excel - find and move program

    I think your WEND statement comes too early - you want the macro to
    continue to do the process WHILE the while condition is true, so I
    THINK the wend needs to be moved to the line before cleanup
    DictatorDraco wrote:
    > i've never touched visual basic before, but i managed to take the source from
    > FindItAll (though i don't think it was the actual source seeing as how it
    > didn't work) and edit it to fit my needs.
    >
    > looking to make a VB macro in Excel that will find a cell and move the
    > entire row that cell is in to the top. doing this for work, and my boss knows
    > i'm not a programmer. i think he wants me to learn. if anyone could point out
    > bugs or tell me commands, it would be much appreciated.
    >
    > here's the code:
    >
    > Sub FindAndMoveToTop()
    > Dim FirstCell As Range
    > Dim NextCell As Range
    > Dim WhatToFind As Variant
    > 'Window prompt allowing user to define WhatToFind
    > WhatToFind = Application.InputBox("What are you looking for?", "Search", ,
    > 100, 100, , , 2)
    > 'If WhatToFind is a value and not blank, move on
    > If WhatToFind <> "" And Not WhatToFind = False Then
    > 'Start with first worksheet
    > Worksheets("Sheet1").Activate
    > 'Start at first cell
    > Range("A1").Select
    > 'Find the first cell containing WhatToFind (specified by user)
    > Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues,
    > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    > MatchCase:=False)
    > 'If FirstCell exists, move on
    > If Not FirstCell Is Nothing Then
    > 'Ok, First Cell is set
    > FirstCell.Activate
    > 'Keep going
    > On Error Resume Next
    > 'NextCell is currently undefined. Don't mistake NextCell for FirstCell
    > While (Not NextCell Is Nothing) And (Not NextCell.Address = FirstCell.Address)
    > 'Find next row containing what is in FirstCell (ActiveCell) and define as
    > NextCell
    > Set NextCell = Cells.FindNext(After:=ActiveCell)
    > 'Don't mistake NextCell for FirstCell, move on
    > If Not NextCell.Address = FirstCell.Address Then
    > 'Activate subsequent NextCells
    > NextCell.Activate
    > End If
    > Wend
    > End If
    > 'Select all rows containing WhatToFind - NOT WORKING ARRGGGHHHH!!! only
    > selecting the cell, not the row.
    > 'Also, if >1 instance of WhatToFind, acts funky...
    > Worksheets("Sheet1").Rows(ActiveCell).Select
    > 'Cut all rows containing WhatToFind
    > Selection.Cut
    > 'Back to A1
    > Cells(1, 1).Select
    > 'Insert cut rows here
    > Selection.Insert Shift:=xlDown
    > 'Clean up
    > Set NextCell = Nothing
    > Set FirstCell = Nothing
    > Range("A1").Select
    > End If
    > End Sub
    >
    >
    > major problem is how to select ALL of the ROWS that WhatToFind is found in.
    > also, if WhatToFind is found in two cells in the same row, it will move the
    > second cell containing it in that row to the next unused row. idk. it acts
    > really funky.
    >
    > i think Worksheets("Sheet1").Rows(ActiveCell).Select is the major problem
    > spot.
    > i think Cells(1, 1).Select might be causing the odd behavior for two cells
    > in the same row.
    >
    > any ideas?



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1