ExcelBanter - Still Having Application.Match trouble

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Still Having Application.Match trouble (https://www.excelbanter.com/excel-programming/308768-re-still-having-application-match-trouble.html)

[email protected]

Still Having Application.Match trouble

It turns out that the problem wasn't the routine at all, rather it was
the fact that my array was more than 5461 elements long (a max in
xl2000 I guess). I've tried to set up an alternatate approach - I'll
post that separately in hopes that someone can help me review - because
its still taking more than 8 hrs to run....

Alan Beban[_2_]

Still Having Application.Match trouble

wrote:

It turns out that the problem wasn't the routine at all, rather it was
the fact that my array was more than 5461 elements long (a max in
xl2000 I guess). I've tried to set up an alternatate approach - I'll
post that separately in hopes that someone can help me review - because
its still taking more than 8 hrs to run....

The following works; perhaps it can be utilized in your program. It
should work so long as the arrays do not have more rows than Excel
worksheets have. Watch for word wrap in your newsreader, though I tried
to allow for that.

Sub Test12345()
Dim a1, a2, a3
Dim ws8 As Worksheet, ws9 As Worksheet
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim i As Long, lb As Long, ub As Long

Set ws9 = Worksheets(9)
Set rng1 = ws9.Range("D1:D4") 'This is formatted as Text and has
'strings 10,11,12, and 13
Set rng2 = ws9.Range("A1:B20000") 'This is formatted as Text and
'has strings 10,10,10,11,11,11,
'12,12,12,12,13,13 in A1:A12
a1 = rng1
a2 = rng2
a3 = ColumnVector(a2, 1)

'Up to this point it's just a matter of loading a1 and a2
'and extracting the column vector; from here on you just
'select an available worksheet and extract the relevant
'bounds on that worksheet

Set ws8 = Worksheets(8)
Set rng3 = ws8.Range("b1:c" & UBound(a1, 1))
Set rng4 = ws8.Range("a1:a20000")
rng4.Value = a3
For i = 1 To UBound(a1, 1)
rng3(i, 1).Formula = "=MATCH(" & a1(i, 1) & "," & rng4.Address & ",0)"
rng3(i, 2).Formula = "=MATCH(" & a1(i, 1) & ", " & rng4.Address & ")"
lb = rng3(i, 1).Value
ub = rng3(i, 2).Value
Debug.Print i, lb, ub
Next
End Sub

All times are GMT +1. The time now is 10:27 PM.