Error 'Type Mismatch' when attempting to use Application.WorksheetFunction.Index

[daheefman]

Hey there everyone

I have a spreadsheet with data in Range B10:P97.
I am attempting to create a single array of the two ranges B10:B97 and O10:O97.

I have read this previous thread heavily
http://www.mrexcel.com/forum/excel-questions/564473-transfer-multiple-ranges-one-array-2.html
but unfortunately I am still suffering from
Run-time Error '13'
Type Mismatch

My code is

Sub Test()
Dim vArr As Variant
vArr = Application.WorksheetFunction.Index(Cells, [row(10:97)], Array(2, 15))
End Sub

The error seems to be thrown due to the [row(10:97)] parameter. When replaced by a single row number there are no issues.

I am using Excel 2013.

Thank you very much for the help.

 

[StephenCrump]

Welcome to the Forum!

This works for me:

vArr = Application.Index(Cells, [row(10:97)], Array(2, 15))

 

[daheefman]

Welcome to the Forum!

This works for me:

vArr = Application.Index(Cells, [row(10:97)], Array(2, 15))

Wow this is working for me too, thank you!
Would you be able to explain when .WeeksheetFunction would be used? In other examples I have seen it done in other examples.

Thanks again!

 

[StephenCrump]

Wow this is working for me too, thank you!
Would you be able to explain when .WeeksheetFunction would be used?

This thread may be helpful in general terms: http://www.mrexcel.com/forum/excel-...ication-vs-application-worksheetfunction.html

In your specific example:

1. In Excel, you can array-enter a formula like this:

=INDEX(1:100, ROW(10:97), {2,15})

2. Pass it to VBA variant like this and you will get a Type Mismatch error:

vArr = WorksheetFunction.Index(Range("1:100"), [row(10:97)], Array(2, 15))

3. But use the Application object instead, and it will work:

vArr = Application.Index(Range("1:100"), [row(10:97)], Array(2, 15))

This looks like an Excel quirk to me. Something gets "lost in translation" using WorksheetFunction.

Perhaps someone else can provide a more technical explanation?