Error during match when using union array

Question

I am trying to get the highest value in non contiguous range and print its address. I used data in Excel like in the picture below:

Sample image

The VBA code I used is:

Sub rngadd()

Dim r1 As Range, r2 As Range, newrng As Range
Dim dblmax As Double
Dim Xrng As Variant

Worksheets("sheet3").Activate
Set r1 = Range("A1:A5")
Set r2 = Range("A8:A12")
Set newrng = Union(r1, r2)
newrng.Select

dblmax = Application.WorksheetFunction.max(newrng)
Xrng = WorksheetFunction.Index(newrng, WorksheetFunction.Match(dblmax, newrng, 0)).address(False, False)
Worksheets(2).Range("D3").Value = dblmax
Worksheets(2).Range("E3").Value = Xrng

End Sub

I get an error as run-time error:

'1004' unable to get the match property of worksheet function class"

but if I run the code just for the range r1, I get the expected result as in the image:

Sample Image2

So I found that I get the error because of using union, the match is unable to perform when there is a break in range(non contiguous range).

What should I do to get the desired result in non contiguous ranges like what I get in contiguous range?

DisplayName · Accepted Answer · 2018-02-19 08:30:43Z

2

use Find() method of Range object:

Sub rngadd()

    Dim r1 As Range, r2 As Range, newrng As Range
    Dim dblmax As Double
    Dim Xrng As Range

    Worksheets("sheet3").Activate
    Set r1 = Range("A1:A5")
    Set r2 = Range("A8:A12")
    Set newrng = Union(r1, r2)
    newrng.Select

    dblmax = Application.WorksheetFunction.Max(newrng)
    Set Xrng = newrng.Find(what:=dblmax, lookat:=xlWhole, LookIn:=xlValues)
    Worksheets(2).Range("D3").Value = dblmax
    Worksheets(2).Range("E3").Value = Xrng.Address

End Sub

But all that Activate/Select is bad practice,and you can also avoid Union() if you already know the ranges addresses in advance (as you currently do)

so please consider the following refactoring:

Sub rngadd()
    Dim newrng As Range
    Dim dblmax As Double

    Set newrng = Worksheets("sheet3").Range("A1:A5, A8:A12")

    dblmax = Application.WorksheetFunction.Max(newrng)

    With Worksheets(2)
        .Range("D3").Value = dblmax
        .Range("E3").Value = newrng.Find(what:=dblmax, lookat:=xlWhole, LookIn:=xlValues).Address
    End With
End Sub

answered Feb 19, 2018 at 8:30

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ADDY Over a year ago

This one, I am just trying as an example. I will try to implement with large data. In that case i will not know the range. The thing I am trying to achieve is I have a range starting from 0, [(1) 0 to Max] value, then [(2) Max to Min] (which is contiguous) value and [(3) Min to 0]. So I will have 1 contiguous range and 2 Non contiguous range. That's why I am trying to use union to make the 2 Non contiguous range into one single contiguous range. Because I need like 2 Ranges which will look like Max - Min and Min - Max for further processing. So any Ideas for that??

DisplayName Over a year ago

In case you didn’t know all single ranges in advance then you can still go with the first code of my answer, i.e. “unioning” them into a “newrng” and then use Find() method on this latter. But you’re encouraged to refactor it in the fashion of the 2nd code

Subodh Tiwari sktneer · Accepted Answer · 2018-02-19 08:30:31Z

2

Index and Match won't work on non-consecutive ranges.

You may loop through the cells to find the max value and it's corresponding cell address like below...

Sub rngadd()

Dim r1 As Range, r2 As Range, newrng As Range, cell As Range
Dim cellAddress As String
Dim maxVal As Long

Worksheets("sheet3").Activate
Set r1 = Range("A1:A5")
Set r2 = Range("A8:A12")
Set newrng = Union(r1, r2)

For Each cell In newrng
    If cell.Value > maxVal Then
        maxVal = cell.Value
        cellAddress = cell.Address(0, 0)
    End If
Next cell

Worksheets(2).Range("D3").Value = maxVal
Worksheets(2).Range("E3").Value = cellAddress

End Sub

answered Feb 19, 2018 at 8:30

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

3 Comments

Subodh Tiwari sktneer Over a year ago

That's true if the max value is figured out at the first place before going into the loop.

user4039065 Over a year ago

Ah yes, I see now how you changed the method.

ADDY Over a year ago

Thanks! I haven't been Into loops in vba yet. I will try to understand it.

Collectives™ on Stack Overflow

Error during match when using union array

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related