98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Index Match function or Vlookup
Index Match function or Vlookup
Resolved · Medium Priority · Version 2016
Fri 4 Jan 2019, 17:05Delegate Quek said...
Quek has attended:
Excel VBA Advanced course
Index Match function or Vlookup
There is an error when i run the sub
Sub VL1()
Sheets("Sheet3").Columns(3).Copy Destination:=Sheets("Sheet4").Columns(1)
Dim LR As Long
Dim FirstArr, SecArr As Variant
Dim c As Range
Dim Var1, Result As Variant
LR = Range("A1").End(xlDown).Row
FirstArr = Sheet2.Range("A1:A" & LR)
SecArr = Sheet2.Range("F1:F" & LR)
Sheet4.Activate
For Each c In Sheet4.Range("B1:B" & LR).Cells
c.Select
c.Value = Application.WorksheetFunction.Index(FirstArr, Application.WorksheetFunction.Match(Sheet4.Range("A1:A" & LR), SecArr, 0), 1)
Next c
Fri 4 Jan 2019, 17:21Trainer Jens said...
RE: Index Match function or Vlookup
Hi Quek,
Thank you for the forum question.
Please let me know which line returns the error and please let me know the error number.
Thanks
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 4 Feb 2019, 14:21Delegate Quek said...
RE: Index Match function or Vlookup
Run-time error '1004':
Unable to get the Vlookup property of the WorkSheetFunction class
Can i also ask whether i can combine a few columns with the same header in different sheets to one sheet?
Thank you.
Regards,
Shi Lin
Mon 4 Feb 2019, 14:46Trainer Jens said...
RE: Index Match function or Vlookup
Hi Shi Lin,
The code you have pasted in the question do not include a Vlookup but Index Match. Does really return:
Run-time error '1004':
Unable to get the Vlookup property of the WorkSheetFunction class
You can get above error when using Vlookup and the Vlookup can't find the lookup value, but there can be other reasons.
Yes you can combine columns to a new sheet. You have a number of options. You can load the data in an array one column at the time, but it has to be an dynamic array. You can change the size of based on a count of records in each column.
Copy and paste is another option, or if you loop through the sheets you can combine them one cell at the time.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 11 Mar 2019, 09:33Delegate Quek said...
RE: Index Match function or Vlookup
Hi Jens,
I did copy one at a time but i would like to create a dynamic function. Are you able to give me an example for the below scenario:
Sheet 1
There are 3 columns ie Name (Column A), Acc Number (Column B), Officer (Column C)
Sheet 2
There are 2 columns i.e. Acc Number (Column A) and Type (Column B)
I would like to map Type (Column B) in Sheet 2 into Sheet 1 as i match it with the Acc Numbers. I would like to put Type in Column D in Sheet 1.
Can you give me an example of how the VBA would look like please?
And you were right about my previous formulas, i was running the values. My dynamic function in defining the lrows is correct but can't seem to read the values as the data set for the application.worksheetfunction. I'm not sure whether because the data is in general format or my language that is the problem.
Mon 11 Mar 2019, 10:18Trainer Jens said...
RE: Index Match function or Vlookup
Hi Shi Lin,
If the problem is that Excel cannot match the Acc number on sheet one with the Acc on sheet two, you will need to find the reason. You can use the Clean function and the Trim function to clean the values.
On the advanced course we used Arrays to map related data.
I would do the same in your situation, but again it will only work if there is a match.
Sub MapSheets()
Dim SheetOneArray as Variant
Dim SheetTwoArray as Variant
Dim RowCounter as Long
SheetOneArray=Sheets(1).Range("a1").CurrentRegion
SheetTwoArray=Sheets(2).RAnge("A1").CurrentRegion
ReDim Preserve SheetOneArray(1 To UBound(SheetOneArrayr, 1), 1 To UBound(SheetOneArray, 2) +1)
For RowCounter = 1 To UBound(SheetOneArray, 1)
SheetOneArray(RowCounter, 3) = Application.WorksheetFunction.VLookup(SheetOneArray(iRow, 2),SheetTwoArray, 2, 0)
Next RowCounter
End Sub
This should work if the Acc numbers are "clean".
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 11 Mar 2019, 10:46Delegate Quek said...
RE: Index Match function or Vlookup
Dear Jens,
I did trim the numbers.
Can i send you a picture of the error?
My email is quek.shilin@uobgroup.com.
Can you email me and i send you the error msg?
And at least one of the examples that can be mapped?
Thanks.
Regards.
Mon 11 Mar 2019, 10:53Trainer Jens said...
RE: Index Match function or Vlookup
Hi Shi Lin,
Please send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 11 Mar 2019, 11:00Delegate Quek said...
RE: Index Match function or Vlookup
Dear Jens,
I just found the reason, was running a test, and apparently when it reaches a value where there is no match, it comes out with the error:
Run-time error '1004':
Unable to get the Vlookup property of the WorkSheetFunction class
Is there a way to put in another VBA instruction to continue running for the whole array despite not finding any matches.
Thanks.
Mon 11 Mar 2019, 12:14Trainer Jens said...
RE: Index Match function or Vlookup
Hi Shi Lin,
Please test the code below.
Sub MapSheets()
Dim SheetOneArray as Variant
Dim SheetTwoArray as Variant
Dim RowCounter as Long
On error resume next
SheetOneArray=Sheets(1).Range("a1").CurrentRegion
SheetTwoArray=Sheets(2).RAnge("A1").CurrentRegion
ReDim Preserve SheetOneArray(1 To UBound(SheetOneArrayr, 1), 1 To UBound(SheetOneArray, 2) +1)
For RowCounter = 1 To UBound(SheetOneArray, 1)
SheetOneArray(RowCounter, 3) = Application.WorksheetFunction.VLookup(SheetOneArray(iRow, 2),SheetTwoArray, 2, 0)
Sheets(1).cells(RowCounter+1,3)=SheetOneArray(RowCounter,4)
Next RowCounter
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 11 Jan 2019: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Create Charts with One keystrokeCreate a graph with one click |
