sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,404
- Office Version
- 2016
- Platform
- Windows
I'm trying to work out how to use a Vlookup alternative by storing values in an array but I can't get it to work, because I don't fully understand the elements.
I have 2 sheets with values in column R. I want to effectively search Sheet 2 for each value in sheet 1 then return the value from column S in sheet 2 to Sheet 1. Vlookup does it fine, but because there are thousands of rows it's taking a while, so I have been tinkering with this;
I know it's not right, and I'm not looking for someone to simply correct it because I'd rather someone explain the different elements of it so I can understand and work out how to adjust it to my needs?
I have 2 sheets with values in column R. I want to effectively search Sheet 2 for each value in sheet 1 then return the value from column S in sheet 2 to Sheet 1. Vlookup does it fine, but because there are thousands of rows it's taking a while, so I have been tinkering with this;
VBA Code:
Sub looking()
Dim arr1() As Variant
Dim arr2() As Variant
Dim arr3() As Variant
LastDataRow = Sheet15.Range("A65000").End(xlUp).row
arr1 = Sheet15.Range("R2:R" & LastDataRow).Value
arr2 = Range("A7:C500").Value
For i = 1 To 1405
arr3(i, 17) = WorksheetFunction.VLookup(arr1(i, 17), 17, False)
Next i
Sheet15.Range("T2:T1405").Value = arr3
End Sub
I know it's not right, and I'm not looking for someone to simply correct it because I'd rather someone explain the different elements of it so I can understand and work out how to adjust it to my needs?