Hi all,

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. Is it possible to copy a row into an array or will I have to
make a multidimensional array and copy the cells individually?
I am currently acheiving this by an autofilter routine but I need to do some
additional things once a string is found, like search upwards to find the
first cell in bold (the header) and copy it to the end of the stored row(or
maybe a variable).
Can anyone help?

Thanks
Marko

Marko

Try Deborah Dalglieshes site Contextures.com. She has an
excellent page on autoFilters. There is also a macro by
Tom Ogilvy that copies filtered data into another sheet.

Regards
Peter Atherton
-----Original Message-----
Hi all,

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. Is it possible to copy a row into an array
or will I have to
make a multidimensional array and copy the cells
individually?
I am currently acheiving this by an autofilter routine
but I need to do some
additional things once a string is found, like search
upwards to find the
first cell in bold (the header) and copy it to the end of
the stored row(or
maybe a variable).
Can anyone help?

Thanks
Marko


.

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Hi all,

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. Is it possible to copy a row into an array or will I have to
make a multidimensional array and copy the cells individually?
I am currently acheiving this by an autofilter routine but I need to do some
additional things once a string is found, like search upwards to find the
first cell in bold (the header) and copy it to the end of the stored row(or
maybe a variable).
Can anyone help?

Thanks
Marko

Thanks for your replies, I'll check them out.

wrote in 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
--------------------------------------------------------------------------
------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------
------------------------------------
Hi all,

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. Is it possible to copy a row into an array or will I have
to
make a multidimensional array and copy the cells individually?
I am currently acheiving this by an autofilter routine but I need to do
some
additional things once a string is found, like search upwards to find the
first cell in bold (the header) and copy it to the end of the stored
row(or
maybe a variable).
Can anyone help?

Thanks
Marko