Index Match function or Vlookup

Resolved · Medium Priority · Version 2016

Fri 4 Jan 2019, 17:05 Delegate 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:21 Trainer 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:21 Delegate 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:46 Trainer 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:33 Delegate 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:18 Trainer 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:46 Delegate 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:53 Trainer 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:00 Delegate 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:14 Trainer 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