简单的VBA:将单元格地址添加到带有Union的范围内吗? [英] Simple VBA: Adding Cell Address to Range with Union?

问题描述

我正在遍历WS中的单元格，并希望将单元格地址添加到范围(或数组)中，因为循环会找到符合条件的单元格.我在最后一行 Set

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)

推荐答案

您的 CellArray 变量未初始化.因此最初是 Nothing ，而 Union 不能将 Nothing 作为参数.

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

此外，您无法访问With对象(此不存在)，因此必须首先将Range更改为变量.

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

可以编写循环体(您必须预先声明 Dim R As Range ):

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

这篇关于简单的VBA:将单元格地址添加到带有Union的范围内吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！