Assigning Autofilter values to a variable

[ripkin900]

Hello everyone I am hoping someone can figure out how to resolve my problem...

I have a spreadsheet with inventory data...
16 columns, column 1 is an inventory item....there are about 4000 different ones all with unique values....
Some are interchangable...
What I do now is I wrote a little macro to find a the item in the spreadsheet...it is below, I use this instead of control F, some peopel in my facility can't even do that... :)



Sub FIND_LAMINATE()

On Error GoTo Terminate

Dim Choice3 As String

Choice3 = InputBox("631 #?", "First String")

Selection.AutoFilter Field:=1, Criteria1:=Choice3

' ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy
'ActiveSheet.Range("$A$1:$Q$4330").AutoFilter Field:=1

'Range("a1").Select

Exit Sub
Terminate:
MsgBox "One of your inputs was incorrect, they must be exact"
End

End Sub


I then highlight it in green and then turn of the autofilter and remember its cell value and refind it....

Then I drill down using its unique value in columns C,D,E and G....

Filtering by these 4 columns gives me matches to substitute....

Is there a way from when I first find it, to take its values in columsn C,D,E and G and turn off the autofilter?

I essentially want to find based on col A.....

Then save its autofiltered values in C,D,E,G and turn off the autofilter saving these columns values and it will drill down to a list of matches..

I think I have to assign those columns autofilter values to a variable and then use those variables to run the autofilter....

I do not know how to do this and hope someone can help me...

I have attached a sample file, Col A values my not match anything as this is just a small sample of the larger file..

Thanks so much in advance....

Jeff

[Bob Phillips]

Something like this

With Selection

With .SpecialCells(xlCellTypeVisible)

vThickness = .Areas(2).Cells(1, 3).Value
vConstruction = .Areas(2).Cells(1, 4).Value
vResin = .Areas(2).Cells(1, 5).Value
vCU = .Areas(2).Cells(1, 7).Value
End With

.AutoFilter
End With

[ripkin900]

So would I incorporate this into existing macro and if so whereabouts?
Or make it a new one?

[Bob Phillips]

If you don't know that, I suggest you do some research on macros, programming, etc. Read a book.

Who wrote that code that you have?

[ripkin900]

Thought I wrote in my post I did...

I'm an accountant not a programmer, if I was a programmer I wouldn't be on an Excel forum asking for help....

I'll figure it out, thanks for the help....