H
Henk Rek
In a message of November 2003 I found this question:
"I would like to search a column ("C") for a string and when found
copy/store
the row (or just the first 12 cells of it at least) and keep searching
till
all occurances are found, and then put the found rows into another
workbook.
I think I will need to put the found rows into an array until the
workbook
is searched.........." The answer (below) did give part of the
solution. I need to copy the found rows + each time the row above to
another sheet.
1. Do I need to make an array and if yes, how do I fill an array?
2. Is there another way to copy the selected/filtered rows + the ones
above to another sheet?
Thanks for any help
Henk
November 2003:""
message:
This routine finds all instances of 'Hello' in the desired range and
highlights cells. You can adapt it to fill an array or simply transfer
whatever data you wish to another sheet. Watch for line wrap.
Sub FindMe()
' Highlights cells that contain "Hello"
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
strToFind = "Hello"
With ActiveSheet.Range("A1:A500")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart, _
LookIn:=xlFormulas)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.Interior.Pattern = xlPatternGray50
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> _
FirstAddress
End If
End With
End Sub
Tested using Excel 97SR2 on Windows 98SE,
HTH
Paul""
END OF MESSAGE of November 2003
"I would like to search a column ("C") for a string and when found
copy/store
the row (or just the first 12 cells of it at least) and keep searching
till
all occurances are found, and then put the found rows into another
workbook.
I think I will need to put the found rows into an array until the
workbook
is searched.........." The answer (below) did give part of the
solution. I need to copy the found rows + each time the row above to
another sheet.
1. Do I need to make an array and if yes, how do I fill an array?
2. Is there another way to copy the selected/filtered rows + the ones
above to another sheet?
Thanks for any help
Henk
November 2003:""
message:
This routine finds all instances of 'Hello' in the desired range and
highlights cells. You can adapt it to fill an array or simply transfer
whatever data you wish to another sheet. Watch for line wrap.
Sub FindMe()
' Highlights cells that contain "Hello"
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
strToFind = "Hello"
With ActiveSheet.Range("A1:A500")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart, _
LookIn:=xlFormulas)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.Interior.Pattern = xlPatternGray50
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> _
FirstAddress
End If
End With
End Sub
Tested using Excel 97SR2 on Windows 98SE,
HTH
Paul""
END OF MESSAGE of November 2003