| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
creating macro VB in Excel - find and move program
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
creating macro VB in Excel - find and move program
|
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
creating macro VB in Excel - find and move program
Try... Worksheets("Sheet1").Rows(ActiveCell.Row).EntireRo w.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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).EntireRo w.Select -- Brassman ------------------------------------------------------------------------ Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290 View this thread: http://www.excelforum.com/showthread...hreadid=547421 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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).EntireRo w.Select -- Brassman ------------------------------------------------------------------------ Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290 View this thread: http://www.excelforum.com/showthread...hreadid=547421 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel macro needed - find and move data | Excel Discussion (Misc queries) | |||
| move excel program from old computer to new | Excel Discussion (Misc queries) | |||
| Macro - Find a value and then move down | Excel Discussion (Misc queries) | |||
| VBA Creating a Macro to Move some of the text from one cell to another | Excel Programming | |||
| VBA Creating a Macro to Move some of the text from one cell to another | Excel Programming | |||
Hybrid Mode
