this code works when I look up a text field but I need it to look up from a field of numbers
so if I have 2 columns 1 is a 4 digit product number the second is the product name, if range A1:A3 looks up product names it works but if I try to do the same for the product code it doesn't (yes I changed the auto filter columns to the relevant field)
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("rom")
Set wsL = Worksheets("data")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("A1:A3")
vCrit = rngCrit.Value
rngOrders.AutoFilter Field:=2, Criteria1:=Application.Transpose(vCrit), Operator:=xlFilterValues
**EDIT**
managed to find solution that works, code for anyone interested, added in split & join. seems to make VBA recognise the numbers as text or something...
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("rom")
Set wsL = Worksheets("data")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("A1:A3")
vCrit = rngCrit.Value
rngOrders.AutoFilter Field:=15, Criteria1:=Split(Join(Application.Transpose(vCrit))), Operator:=xlFilterValues
Bookmarks