Get specific item in a range-CodePudding

This is my code:

    Debug.Print "Full range: " & searchResult.Address
    Debug.Print "Size of range: " & searchResult.Count
    Dim cell As Range
    For Each cell In searchResult
        Debug.Print "from for each: " & cell.Address
    Next cell
    Debug.Print "from item 0: " & searchResult.item(0).Address
    Debug.Print "from item 1: " & searchResult.item(1).Address
    Debug.Print "from item 2: " & searchResult.item(2).Address

This is my output:

Full range: $M$125,$M$148,$M$161
Size of range: 3
from for each: $M$125
from for each: $M$148
from for each: $M$161
from item 0: $M$124
from item 1: $M$125
from item 2: $M$126

The for each is giving me the result I want, while the "item" give just the next item within the worksheet, not into that range. I just want to get the address of a specific item in that range. Let's say something like "searchResult(2).address = $M$161" (in case it's 0-based) like you would access items in an array. I'm sure it's very simple but can't find what's the name of the property I need to use... Thanks for your help.

CodePudding user response：

A discontinuous range does not return a continuous array...

In order to convert it in a 1D array (values and addresses), please try the next function. It iterates between all the range areas and all cells of each area:

Function DiscRangeToArray(rng As Range) As Variant
   Dim A As Range, c As Range, arrVal, arrAddress, i As Long
   ReDim arrVal(rng.cells.count - 1): ReDim arrAddress(rng.cells.count - 1)
   For Each A In rng.Areas
        For Each c In A.cells
            arrVal(i) = c.value: arrAddress(i) = c.Address: i = i   1
        Next c
    Next A
    DiscRangeToArray = Array(arrVal, arrAddress)
End Function

You can test it using the next testing Sub:

Sub testDiscRangeToArray()
   Dim rng As Range, jgArr
   Set rng = Range("$M$125,$M$148,$M$161")
   jgArr = DiscRangeToArray(rng)
   Debug.Print Join(jgArr(0), "|")
   Debug.Print Join(jgArr(1), "|")
   
   'For a range not having only one cell in an area:
   Set rng = Range("A2:A3,B4,C5:D6")
   jgArr = DiscRangeToArray(rng)
   Debug.Print Join(jgArr(0), "|")
   Debug.Print Join(jgArr(1), "|")
End Sub

Please, test it and send some feedback.

CodePudding user response：

Thanks to FaneDuru's comment I could find the solution. Doing:

searchResult.Areas.item(1).Address

Did the job in my case.