How to define a two-dimensional array's column like a range?

Question

If I have a one-dimensional array as shown below, it treats the array like a range:

Option Explicit
Sub test()

Dim Numbers(1 To 10) As Long
Dim z As Long
Dim m As Variant

z = 10

Numbers(10) = 10

m = Application.Match(z, Numbers, 0)
MsgBox m

End Sub

But as a 2-dimensional array, I do not know how to treat as a range and it returns a Type Mismatch error:

Option Explicit
Sub test()

Dim Numbers(1 To 10, 1 To 2) As Long
Dim z As Long
Dim m As Variant

z = 10

Numbers(10, 2) = 10

m = Application.Match(z, Numbers, 0)
MsgBox m

End Sub

I am wondering if there is something like a method such as Numbers.Column(2) or something that I can use

score 3 · Accepted Answer · answered Feb 14 '19 at 11:19

3

If you want to search the second column, you could use Index to get at that:

m = Application.Match(z, Application.Index(Numbers, 0, 2), 0)

for example.

answered Feb 14 '19 at 11:19

Rory

32,730
5
32
35

Nice one, never thought about using that solution on arrays! – Pᴇʜ Feb 14 '19 at 11:23

score 1 · Answer 2 · answered Feb 14 '19 at 11:16

The "Type Mismatch" actually comes because Match returns an error. You must convert it into a string MsgBox CStr(m) and it will show Error 2042.

This doesn't work because the second parameter of the Match method must either be one column or one row. This is because Match can either search in a column (and return the row number) or search in a row (and return the column number). But if it is a 2D range it does not know if it should return the column or the row number.

You defined it as 2D range and therfore it fails.

Eg. The following would work, because it represents one column only.

Dim Numbers(1 To 10, 1 To 1) As Long
Numbers(10, 1) = 10

How to define a two-dimensional array's column like a range?

2 Answers2