Is there a limit to read a table array as range in excel?

Question

newbie in vba macro here, I am trying to play around with the vba code i found in the link attached below but it is not working when i have a huge range

I have a vba function that works on a say range C16000:C16954 but not range C116000:C116954, although they read the same number of rows

    FuzzyvLookup(B2,D16000:D16954,1) = "SOME VALUE" 
    FuzzyvLookup(B2,D116000:D116954,1) = #N/A when i expect it to be 
    FuzzyvLookup(B2,D116000:D116954,1) = "SOME VALUE"

Any suggestions on why so?

Added the link to the macro code I was referring to: (it is really long so i didn't paste it here!) https://www.mrexcel.com/board/threads/fuzzy-matching-new-version-plus-explanation.195635/

what excel version do you have? how are you exactly calling the function? What are you doing with the function return? What error do you get? — Ricardo Diaz, Jan 16 '20 at 02:45
For starters, change all the instances of `Integer` to `Long`. Using `Integer` is bad practice here. — BigBen, Jan 16 '20 at 03:02
@RicardoDiaz hi, im using excel 2016, im using the function as such FuzzyvLookup(B2,D16000:D16954,1), comparing a value vs a column/range of values to see the closest match. It seems like it is a value not available error — user12716476, Jan 16 '20 at 03:17
@BigBen hi, why is it a bad practice? will change the instances! thank you for the tip — user12716476, Jan 16 '20 at 03:18
See [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) why - but that code is like 14 years old so maybe the developer gets a pass. — BigBen, Jan 16 '20 at 03:20
That code would fail with any range on rows >=32767. So even back then only half the sheet was accessable! So yes, change _all_ uses of `Integer`to `Long` — chris neilsen, Jan 16 '20 at 03:27
@BigBen hi thank you for the knowledge! very useful for my future codes :) — user12716476, Jan 16 '20 at 03:33
@chrisneilsen hi, oh no, do you know why is that the case? also , do you have any suggestions for me to tackle this problem? cos i have 169548 active rows now — user12716476, Jan 16 '20 at 03:36
See [this](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary) for the limits of `Integer`, which are `-32,768 to 32,767`. Excel has more rows than `Integer` can handle. Literally do a find/replace on `Integer`, changing to `Long`. — BigBen, Jan 16 '20 at 03:40
@BigBen I just tested the function again after changing to long, it works! thank you!!! — user12716476, Jan 16 '20 at 03:50
@chrisneilsen thank you for helping also! it's really effective :) — user12716476, Jan 16 '20 at 03:50

user11982798 · Answer 1 · 2020-01-20T04:22:14.600

you can do like this:

Function FuzzyvLookup(myRange As range, strLookup As String, lookupType)

    ' lookup type
    ' 1 first
    ' 2 mid
    ' 3 last
    ' 0 all

    Dim a() As Long
    myRow = 2
    myCol = 2
    ReDim a(myRow - 1, myCol - 1) As Long
    a(0, 0) = 9
    a(1, 0) = 6
    a(0, 1) = 9
    a(1, 1) = 6
    Dim myVal
    myVal = ""
    For b = 1 To myRange.Rows.Count
        If lookupType = 1 And myRange.Item(b, 1) Like strLookup & "*?" Then
            myVal = myRange.Item(b, 2)
            Exit For
        End If
        If lookupType = 2 And myRange.Item(b, 1) Like "?*" & strLookup & "*?" Then
            myVal = myRange.Item(b, 2)
            Exit For
        End If
        If lookupType = 3 And myRange.Item(b, 1) Like "?*" & strLookup Then
            myVal = myRange.Item(b, 2)
            Exit For
        End If
        If lookupType = 0 And myRange.Item(b, 1) Like "*" & strLookup & "*" Then
            myVal = myRange.Item(b, 2)
            Exit For
        End If
    Next b
    FuzzyvLookup= myVal
End Function

in your cell can test this macro:

Macro1(D16000:D16954, B2, 1)

remark: 1 mean first (begin from character 1 of the string)

Is there a limit to read a table array as range in excel?

1 Answers1