Trying to use vlookup between sheets

[Eric Penfold]

My VLookup code is not doing anything??

Sub VLookup_Min()


Dim Wb        As Workbook
Dim Src       As Worksheet, Des As Worksheet
Dim SrcLRow   As Long, DesLRow
Dim x         As Variant
Dim SrcRng    As Range, DesRng As Range

Set Wb = ThisWorkbook
Set Src = Wb.Worksheets("Intact Detail")
Set Des = Wb.Worksheets("Alton")
SrcLRow = Src.Range("A" & Rows.Count).End(xlUp).Row
DesLRow = Des.Range("A" & Rows.Count).End(xlUp).Row
Set DesRng = Des.Range("A2:A" & DesLRow)
Set SrcRng = Src.Range("A2:A" & SrcLRow)

Src.Range("O2:O" & SrcLRow).AutoFilter Field:=1, Criteria1:="<>"

For Each x In SrcRng.SpecialCells(xlCellTypeVisible)
On Error Resume Next
    Src.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
    Src.Range("O" & x).Value, DesRng, 8, False)
Next x

If Src.AutoFilterMode Then Src.AutoFilter.ShowAllData

End Sub

 

[Alex Blakenburg]

Your DesRng is just column A (1 column) and you are asking for it to return column 8.
The resume next is masking the error.

 

[Eric Penfold]

Your DesRng is just column A (1 column) and you are asking for it to return column 8.
The resume next is masking the error.

Thanks that`s sorted now A to I but next issue is the code below says error "method object of range worksheet failed"?

Src.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
    Src.Range("O" & y).Value, DesRng, 8, False)

 

[Alex Blakenburg]

You original code did not use "y" and the original code does not define or set "y"

 

[Eric Penfold]

Sorry back to x but still not working

Src.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
    Src.Range("O" & x).Value, DesRng, 8, False)

 

[Alex Blakenburg]

You are going to need to show us what the sheet Source looks like.
I think you are intending to filter on Column O but if you click on the heading of Column O and hit Ctrl+A, if that selectes multiple column then the filter is being applied to whatever is the first column of what is now highlighted.

Secondly you are using x as if it is a row number it is actually a cell in column A, so on the left side of the "=" it would not be
Src.Range("A" & x).Value =
it would just be
x.Value =
and
Src.Range("O" & x).Value
would be
Src.Range("O" & x.Row).Value,

 

[Eric Penfold]

Thanks for the help above but still not working here is the source range

Product	Description	Branch Available STK	Category Code	Type	Company Effective STK	Total Company STK	Last Cost Actual	V Line Special	Default Supplier	Manufacturer	Alternative Code	Maximum Stock Level	Minimum Stock Level	Sales Quantity
SPECIAL	Special	-3	SPECIAL	Special	292	9	61.07	No	No Default Supplier			0	0	464
12777	Frost Body B19 Medium Sump MJ 100mm Outlet	2	11	Standard	379	329	11.29	No	HEBEI METALS & MINERALS CORP LTD			0	0	1
12798	Frost Body B21 side outlet.	1	11	Standard	123	83	7	No	HEBEI METALS & MINERALS CORP LTD			0	0
13034	Frost Body B15 (cast iron) BSP 4" Internal (Female) Thread	1	11	Standard	354	254	11.86	No	HEBEI METALS & MINERALS CORP LTD			0	0
13300	Frost Body B19 external 4 inch NPS(Male) External Thread	1	11	Standard	119	19	0.9383	No	HEBEI METALS & MINERALS CORP LTD			0	0
13333	Frost Body B22 BSP 4" Side Internal (Female) Thread	1	11	Standard	201	1	19.65	No	HEBEI METALS & MINERALS CORP LTD			0	0
13610	Frost Body B2 MJ 150mm Outlet (M6 version)	1	11	Standard	143	43	13.3291	No	HEBEI METALS & MINERALS CORP LTD			0	0
13613	Frost Body B2 BSP 4" Internal (Female) Thread (M6 version)	1	11	Standard	476	326	24	No	HEBEI METALS & MINERALS CORP LTD			0	0
15049	Frost Perforated Filter pipe for 33.0454.000 planter drain assembly 150mm long	1	11	Standard	108	108	20.43	No	Fine Line Sheet Metal Engineering Ltd			0	0
150RTWP	150mm Twinwall Duct x6m; Purple	11	TWDU	Standard	11	11	18.49	Yes	Naylor Drainage Ltd			0	0
20VGGRID	20/20 Biaxial Geogrid 3.95x50m Black	24	GEOM	Standard	182	67	147.44	No	Taian Road Engineering Materials co. Ltd			16	16	26
225TWEC	225mm Twinwall Pipe End Cap	2	TWDU	Standard	22	22	10.45	Yes	No Default Supplier			0	0	7
300TH	300mm Dia Top Hat	1	TWD	Standard	1	1	23.15	Yes	No Default Supplier			5	2
30VGGRID	30/30 Biaxial Geogrid 3.95x50m Black	17	GEOM	Standard	125	50	202.94	No	Taian Road Engineering Materials co. Ltd			18	10	10

 

[Eric Penfold]

And here is the destination range

Product Code	Product Description Line 1	Category.Code	Purchasing.PackSize	Purchasing.DefaultSupplier	StockingStatus	P_CURRENTB.MinimumStockLevel	Supplier Lead Time	Min Stock This Month
025KGL	Lubricant Tube 250g	ADITB	10	DAV001	Stocked	8	5
9001	Plasson Concentric Water Meter	WATERF	1	CIR001	Stocked	1	4
20VGGRID	20/20 Biaxial Geogrid 3.95x50m Black	GEOM	9	TAI001	Stocked	16	12
300TH	300mm Dia Top Hat	TWD	1	NONE	Stocked	2	4
30VGGRID	30/30 Biaxial Geogrid 3.95x50m Black	GEOM	9	TAI001	Stocked	10	12
315VC4	315mm Underground Drainage Inspection Chamber Base 5no.110mm Inlet (inc. 2 Socket Plugs)	UDR	48	BRE002	Stocked	15
4VF78	110mm Flexible Spigot & Socket fit Rain/Waste Adaptor	UDR	1	VIP001	Stocked	200	2
4VFWS	110mm Rubber Wall Seal (Requires 140mm dia core drill)	FLEXSEAL	1	VIP001	Stocked	15	2
6VC160	600mm Vision 600 Chamber Base Swept Cross 160mm pipework	US	8	INT001	Stocked	100	30
6VC160S	600mm Vision 600 Chamber Base 160mm Straight Channel	US	5	INT001	Stocked	8	30
6VC250	600mm Vision 600 Chamber Base Swept Cross 250mm pipework	US	8	INT001	Stocked	200	30
6VC250S	600mm Vision 600 Chamber Base 250mm Straight Channel	US	5	INT001	Stocked	8	30
6VC315	600mm Vision 600 Chamber Base Swept Cross 315mm pipework	US	1	INT001	Stocked	50	30
6VCA10150	250mm Vision 600 chamber, outlet adaptor to 150mm TW – Socket	US	1	NAY001	Stocked	20	10

 

[Alex Blakenburg]

Did you accidentally copy the same data twice ?
At the moment you are Filtering the sheet "Intact Detail".
I believe you intend to filter on column O but you are really filtering on Column A

You are then looking up sheet Alton using the value in column O in Intact Detail which is Sales Quantity.
(That seems an odd thing to use for a lookup value)
You are then looking up sheet Alton. Column O of Intact Detail>Sales Quantity is never going to find a match in what you are showing as the Alton Sheet which has product as the first column.

How about getting your Vlookup to work in the spreadsheet itself and then post it here and we can convert it to a VBA worksheet function ?

Also I would have considered what you have as being Src to be the Destination since it is where the Vlookup and it's return value go.

 

[Eric Penfold]

I got it working in excel but can`t get it to work in VBA. See Excel formula '=VLOOKUP(A2,'Intact Detail'!A2:O2,15,FALSE)

Sub VLookup_Min()

Dim Wb        As Workbook
Dim Src       As Worksheet, Des As Worksheet
Dim SrcLRow   As Long, DesLRow
Dim x         As Variant
Dim SrcRng    As Range, DesRng As Range

Set Wb = ThisWorkbook
Set Src = Wb.Worksheets("Intact Detail")
Set Des = Wb.Worksheets("Alton")
SrcLRow = Src.Range("A" & Rows.Count).End(xlUp).Row
DesLRow = Des.Range("A" & Rows.Count).End(xlUp).Row


Src.Range("O2:O" & SrcLRow).AutoFilter Field:=1, Criteria1:="<>"

Set DesRng = Des.Range("A2:I" & DesLRow).SpecialCells(xlCellTypeVisible)
Set SrcRng = Src.Range("A2:A" & SrcLRow).SpecialCells(xlCellTypeVisible)

For Each x In SrcRng
On Error Resume Next
    x.Value = Application.WorksheetFunction.VLookup( _
    Src.Range("O2" & x.Row).Value, DesRng, 15, False)
Next x

If AutoFilter = True Then
AutoFilter = False
End If

End Sub