search for values to define range

[bostitch]

I am trying to write a macro that will search a
spreadsheet for a number in column 1 (for example 1303002
only), and then highlight and copy all the associated data
in column 5. Can anyone provide an example? (VLOOKUP is
not desireable for this task.)


Thank you.

Element LC Nx Ny Nxy
1303001 6 -9218 0 518
1303002 1 -8645 0 227
1303002 2 -9141 0 205
1303002 3 -8102 0 448
1303002 4 -8719 0 192
1303002 5 -15747 0 613
1303002 6 -15661 0 509
1303003 etc...

 

[Don Guillett]

look in vba HELP index for FIND

 

[steve]

The easiest way would be to do an auto filter than copy visible cells and
than paste

Columns("A:A").AutoFilter Field:=1, Criteria1:="1303002"
' you can use an input box to get the criteria and make it a variable

Range("E1:E539").SpecialCells(xlCellTypeVisible).Copy
' I chose rows 1:539, make them what you will. But be sure to
' restrict the rows or you'll get more than you want. You can also make
' your range a variable.