Simple VBA: Adding Cell Address to Range with Unio

Question

I am looping through cells in my WS and want to add the cell address to a range (or array) as the loop finds cells which meet a criteria. I get an Object Requried error at the last line Set

Dim CellArray As Range
With ws
    With .Cells(Application.WorksheetFunction.Match("Total checks", .Range("A:A"), 0), Application.WorksheetFunction.Match(ComboBox1.Value,0))
        .Formula = "=SUM('" & Root & sourceSheet & ws.Name & " " & monthNumber & "." & lastDay & "." & Format(Now(), "yy") & "'!$H:$H)"
        Set CellArray = Union(CellArray, This.Address)

Answer 1

try this

please do not use "With" command because it makes code hard to read in most situations (like yours)

Sub test()

    Dim ws As Worksheet
    Set ws = ActiveSheet            ' or point to any worksheet here, if you want  

    Dim CellArray As Range

    Dim myCell As Range             ' range("a:a") must contain "Total checks", otherwise next line will fail
    Set myCell = ws.Cells(Application.WorksheetFunction.Match("Total checks", ws.Range("A:A"), 0), Application.WorksheetFunction.Match(ComboBox1.Value, 0))

    myCell.Select                   ' debug ... visually verify the cell location

    myCell.Formula = "=SUM('" & Root & sourceSheet & ws.Name & " " & monthNumber & "." & lastDay & "." & Format(Now(), "yy") & "'!$H:$H)"

    If CellArray Is Nothing Then
        Set CellArray = myCell      ' CellArray cannot be empty to start, otherwise the "union" function will fail
    Else
        Set CellArray = Union(CellArray, myCell)
    End If

    CellArray.Select                ' debug ... visually verify the resulting CellArray range

End Sub

Answer 2

Your CellArray variable is not initialized. It's thus initially Nothing, and Union can't take Nothing as an argument.

Also, you can't access the With object (This does not exist), so you have to affect the Range to a variable first.

The loop body could be written (you have to declare Dim R As Range beforehand):

Set R = Cells(Application.WorksheetFunction.Match("Total checks", .Range("A:A"), 0), Application.WorksheetFunction.Match(ComboBox1.Value,0))

R.Formula = "=SUM('" & Root & sourceSheet & ws.Name & " " & monthNumber & "." & lastDay & "." & Format(Now(), "yy") & "'!$H:$H)"

If CellArray Is Nothing Then
    Set CellArray = R
Else
    Set CellArray = Union(CellArray, R)
End If