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?

Try...

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

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
[email protected]
"DictatorDraco" <[email protected]> wrote in message
news:[email protected]...
> 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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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
>
>

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
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

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
> [email protected]
> "DictatorDraco" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

Did for me.

--
HTH

Bob Phillips

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

"DictatorDraco" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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?
> >
> >
> >

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

--
Don Guillett
SalesAid Software
[email protected]
"DictatorDraco" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> "DictatorDraco" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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?
>>
>>
>>

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?