Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find all then paste not working.
Here is the situation. I have this code and it is working fine except one
thing. I would like to paste all of the cells that are found, as it stands now it is only finding and pasting the first instance of a word. ie. if you search for a word that is in the ss twice it is only finding the first one. Thanks Code: Private Sub cmdFind_Click() Dim wksCopyFrom As Worksheet Dim wksCopyTo As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngCopyTo As Range Set wksCopyTo = Sheets("Search_Add") For Each wksCopyFrom In Worksheets If wksCopyFrom.Name < wksCopyTo.Name And _ wksCopyFrom.Name < "Search_Add" Then 'sheets you don't want searched Set rngToSearch = wksCopyFrom.Cells Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, LookAt:=xlPart) If Not rngFound Is Nothing Then 'If rngCopyTo.Offset.Value < "" Then 'rngFound.Offset(1, 0).EntireRow.Insert Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngFound.EntireRow.Copy rngCopyTo End If End If 'End If Next wksCopyFrom txtFind.Text = "" Sheets("Search_Add").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find all then paste not working.
Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet Dim wksCopyTo As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngCopyTo As Range Dim sAddr as String Set wksCopyTo = Sheets("Search_Add") For Each wksCopyFrom In Worksheets If wksCopyFrom.Name < wksCopyTo.Name And _ wksCopyFrom.Name < "Search_Add" Then 'sheets you don't want searched Set rngToSearch = wksCopyFrom.Cells Set rngFound = rngToSearch.Find(txtFind.Text, _ , xlValues, LookAt:=xlPart) If Not rngFound Is Nothing Then sAddr = rngFound.Address do Set rngCopyTo = wksCopyTo.Cells(Rows.Count, _ "A").End(xlUp).Offset(1, 0) rngFound.EntireRow.Copy rngCopyTo set rngFound = rngToSearch.FindNext(rngFound) loop while sAddr < rngFound.Address End If End If Next wksCopyFrom txtFind.Text = "" Sheets("Search_Add").Activate End Sub -- Regards, Tom Ogilvy "Mark Cover" wrote in message ... Here is the situation. I have this code and it is working fine except one thing. I would like to paste all of the cells that are found, as it stands now it is only finding and pasting the first instance of a word. ie. if you search for a word that is in the ss twice it is only finding the first one. Thanks Code: Private Sub cmdFind_Click() Dim wksCopyFrom As Worksheet Dim wksCopyTo As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngCopyTo As Range Set wksCopyTo = Sheets("Search_Add") For Each wksCopyFrom In Worksheets If wksCopyFrom.Name < wksCopyTo.Name And _ wksCopyFrom.Name < "Search_Add" Then 'sheets you don't want searched Set rngToSearch = wksCopyFrom.Cells Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, LookAt:=xlPart) If Not rngFound Is Nothing Then 'If rngCopyTo.Offset.Value < "" Then 'rngFound.Offset(1, 0).EntireRow.Insert Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngFound.EntireRow.Copy rngCopyTo End If End If 'End If Next wksCopyFrom txtFind.Text = "" Sheets("Search_Add").Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find all then paste not working.
Tom,
Works like a charm.. you are the man thinks so much for your help. "Tom Ogilvy" wrote: Private Sub cmdFind_Click() Dim wksCopyFrom As Worksheet Dim wksCopyTo As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngCopyTo As Range Dim sAddr as String Set wksCopyTo = Sheets("Search_Add") For Each wksCopyFrom In Worksheets If wksCopyFrom.Name < wksCopyTo.Name And _ wksCopyFrom.Name < "Search_Add" Then 'sheets you don't want searched Set rngToSearch = wksCopyFrom.Cells Set rngFound = rngToSearch.Find(txtFind.Text, _ , xlValues, LookAt:=xlPart) If Not rngFound Is Nothing Then sAddr = rngFound.Address do Set rngCopyTo = wksCopyTo.Cells(Rows.Count, _ "A").End(xlUp).Offset(1, 0) rngFound.EntireRow.Copy rngCopyTo set rngFound = rngToSearch.FindNext(rngFound) loop while sAddr < rngFound.Address End If End If Next wksCopyFrom txtFind.Text = "" Sheets("Search_Add").Activate End Sub -- Regards, Tom Ogilvy "Mark Cover" wrote in message ... Here is the situation. I have this code and it is working fine except one thing. I would like to paste all of the cells that are found, as it stands now it is only finding and pasting the first instance of a word. ie. if you search for a word that is in the ss twice it is only finding the first one. Thanks Code: Private Sub cmdFind_Click() Dim wksCopyFrom As Worksheet Dim wksCopyTo As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngCopyTo As Range Set wksCopyTo = Sheets("Search_Add") For Each wksCopyFrom In Worksheets If wksCopyFrom.Name < wksCopyTo.Name And _ wksCopyFrom.Name < "Search_Add" Then 'sheets you don't want searched Set rngToSearch = wksCopyFrom.Cells Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, LookAt:=xlPart) If Not rngFound Is Nothing Then 'If rngCopyTo.Offset.Value < "" Then 'rngFound.Offset(1, 0).EntireRow.Insert Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngFound.EntireRow.Copy rngCopyTo End If End If 'End If Next wksCopyFrom txtFind.Text = "" Sheets("Search_Add").Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
Cut and Paste Stopped Working | Excel Discussion (Misc queries) | |||
Paste Special Not Working | Excel Worksheet Functions | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
Worksheet_Change not working when using paste | Excel Programming |