ripkin900
02-04-2012, 10:09 PM
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
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