Need to write a macro

[Ray]

I need to write a macro that will search through several groups of cells in
the same column, depending on the values in those cells and return several
cell values, in the same rows as the cells in the search column, to another
sheet in the same workbook.

Example:

Column K (string data type ) is the column that must be searched for a
certain string value.

Column C will contain numbers, like range C1:C9 all contain a value of 1
(the range could be smaller or larger), Range C10:C16 all contain a value of
2 (again the range could be smaller or larger), etc.etc..

Columns A (text), B (date), F(integer), and O(decimal) are the cell values
that need to be sent to the other sheet.

So, the macro would start searching through Column K (K1:K10000 for example)
while Column C is the same value. It is searching for a string value that is
specified in the macro ( I will be copying this macro several times, each
one having a different string value for the search). When it encounters the
search value in Column K it then does a copy of the Cells A,B,F, and O from
the same row and inserts them in the other sheet (inserts a new row at row
10 and pastes the cell values in cells A10,B10,C10, and D10) then goes back
and continues searching Column K until the value in Column C changes. At
that point it will go to the other sheet and insert a blank row, then go
back and start searching Column K again at the row where the Column C value
changed, copying and inserting the values in the other sheet until the
Column C value changes again, insert a blank row in the other sheet, return
and continue searching until the end of the Column K range (K10000)

This is similar to a query in a database where the query looks for a value
and returns the cell values specified for all the rows where the value is
present.

 

[Bernie Deitrick]

Ray,

The macro below should get you started. Change

myA = Array("First", "Second", "Third")

to include all the values in column K that you want to extract.

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim myR As Range
Dim myA As Variant
Dim myV As Variant
Dim myC As Range
Dim myS As Worksheet

Set myC = Intersect(Range("2:" & Rows.Count), Range("A:B,F:F,O:O"))

'Here are all the values to find in column K
myA = Array("First", "Second", "Third")
Set myR = Range("A1").CurrentRegion

On Error Resume Next
Worksheets("Summary").Delete
Worksheets.Add.Name = "Summary"

For Each myV In myA
myR.AutoFilter Field:=Range("K:K").Column, Criteria1:=myV
Intersect(myR, myC).SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2)
Next myV

End Sub

 

[Ray]

Bernie,

This is far less complicated than I had thought it would be. It appears to
me that using your code would enable me to use only one macro to search for
several different string values in Column K, sequentially, instead of having
to copy the macro several times, assigning a different search value for each
macro.

Am I correct in understanding that if I change:

myA = Array("First", "Second", "Third")
to:
myA = Array("Clm5000n2y", "Md10000", "Alw10000s")

the macro would search column K for string value "Clm5000n2y" first and
then, after searching every cell in column K for that string value and
inserting the associated values (cells A,B,F, and O) in sheet "Summary",
would then start again, searching Column K for string value "Md10000",
inserting those associated values (cells A,B,F,and O) in sheet "Summary" and
finally, searching for "Alw10000s" in column K and inserting those
associated values (cells A,B,F,and O) in sheet "Summary"? If my assumptions
are correct this will work great! However, I fail to see code that would
insert a blank row in sheet "Summary" each time the value in Column C
changes. This is not absolutely necessary but would make analysis of sheet
"Summary" easier. I will also add cell K to the range for "myC " in order to
know which column K value was used for each set of values in sheet
"Summary".

Thank you for your input,
Ray

 

[Ray]

Bernie,

I created the macro using your code. I changed "myA" to include the string
values to search for in column K. I also changed "myC" range to:
Range("A,F:F,K:K,O:O") adding cells C, D, and K to the insert for
sheet"Summary". Cell C and D are there but cell K isn't. Is this because K
is the search column? Is there any way I can include cell K in the insert?
Also, the macro returns data from every row not just the rows where the
value in cell K matches the criteria, and, it appears to be doing the
routine 3 times. The test data I started with contained 103 rows to be
searched but the macro inserted 306 rows in sheet "Summary", skipping row 1
in each of the 3 iterations. (?)

Ray

 

[Dave Peterson]

I used Bernie's code and it worked fine in my tests in xl2003.

You may want to post your current code--maybe you changed too much???

 

[Bernie Deitrick]

In this code, I have assumed that you have a table starting in cell A1 that is contiguous, extending
to at least column O, without any entirely blank rows or columns within it. The code works perfectly
in my sample workbook, and now adds extra lines, as well as the headings from Row 1 (also an
assumption):

Sub Macro2()
Dim myR As Range
Dim myA As Variant
Dim myV As Variant
Dim myC As Range
Dim myS As Worksheet
Dim myH As Range

Set myH = Range("A,F:F,K:K,O:O")

Set myC = Intersect(Range("2:" & Rows.Count), myH)

'Here are all the values to find in column K
myA = Array("Clm5000n2y", "Md10000", "Alw10000s")
Set myR = Range("A1").CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True
Worksheets.Add.Name = "Summary"

Intersect(myR.Parent.Range("1:1"), myH).Copy _
Worksheets("Summary").Cells(1, 1)

For Each myV In myA
myR.AutoFilter Field:=Range("K:K").Column, Criteria1:=myV
Intersect(myR, myC).SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp)(3)
Next myV

myH.Parent.ShowAllData

Worksheets("Summary").Range("A2").EntireRow.Delete
End Sub


HTH,
Bernie
MS Excel MVP

 

[Ray]

Bernie,

I replaced the original code with your latest code. Columns K and O only get
inserted in row 1, not in any of the other rows. (I don't have column
headers yet, the data starts in row 1). Also, the code is still copying all
rows, not just the ones where the search value is found, and the macro is
inserting 309 rows into sheet "Summary" when I only have 103 rows to search.

Ray

 

[Dave Peterson]

How can column K only use row 1?

I would think that if you're applying a filter to column K, then every row
should have something in it.

And for the autofilter to be effective, you'll want to add headers in row 1.
Since your code only copies rows 2:lastrow (visible rows only--after the
autofilter has been applied), you could be missing row 1 -- if it matches one of
the values in the array.

I'd add a line to Bernie's code:
On Error Goto 0 '<-- maybe a masked error is causing trouble.

In fact, I'd add another line right near the top:

Activesheet.autofiltermode = false

To remove any existing autofilter on the sheet.

 

[Bernie Deitrick]

Ray,

Is rbaker 5 2 9 @ msn dot com your actual email address, and can it accept
small attachments with macros? If it is, I will send you a working example.

Bernie

 

[Ray]

Bernie, yes, that is my email and it should accept your example.

I think you aren't understanding exactly what I'm wanting the macro to do. I
may have to send you a screenshot with a further explanation of what I am
doing and what I need.

Ray

 

[Bernie Deitrick]

Ray,

I have sent you the working example. Let us know if it helps.

HTH,
Bernie
MS Excel MVP