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:

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:

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?

Answer 1

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

Answer 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