时间戳

首页 > 解决方案 > VBA - 使用“Set”和“=”分配范围对象时的不同.address

excel - VBA - 使用“Set”和“=”分配范围对象时的不同.address

问题描述

我有这段代码，但我不理解这种特殊行为：

1. 如果我通过“Set”关键字将范围分配给 rngSearch，那么 debug.print 会显示我期望的结果：$P$1:$AI$1

2. 如果我通过“=”运算符将范围分配给 rngSearch（并注释掉 1.），则 debug.print 将显示：$P$1

因为在这两个中我总是引用相同的范围 .Range(.Cells(1, lnFirstCol), .Cells(1, lnLastCol)) 我不明白 2. 他不接受它。我假设他忽略了任务并坚持第一个任务。你能解释一下为什么会这样吗？谢谢！

Sub CreateFile()

Dim NewBook         As Workbook
Dim SheetToCopy     As String

SheetToCopy = "MasterVBATest"

ThisWorkbook.Sheets(SheetToCopy).Copy After:=Worksheets("Cockpit")
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Dim rngSearch As Range
Dim lnFirstCol As Long
Dim lnLastCol As Long
Dim ValueToFind As Variant

ValueToFind = "Trenner"

With ActiveSheet

    lnLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    'First assignment
    Set rngSearch = .Range(.Cells(1, 1), .Cells(1, lnLastCol)).Find(What:=ValueToFind, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
    Debug.Print rngSearch.Address 'Result: $P$1 --> expected
    If rngSearch Is Nothing Then
        Debug.Print ValueToFind & " was not found."
    Else
        lnFirstCol = rngSearch.Column
    End If
     
    '1.
    'Set rngSearch = .Range(.Cells(1, lnFirstCol), .Cells(1, lnLastCol))
    'Debug.Print rngSearch.Address ' Result: $P$1:$AI$1 --> expected
    
    '2.
    rngSearch = .Range(.Cells(1, lnFirstCol), .Cells(1, lnLastCol))
    Debug.Print rngSearch.Address ' Result: $P$1

End With

结束子

标签： excelvbarangeoperators

解决方案

来吧，可能会消除你的困惑..

.Range(.Cells(1, 1), .Cells(1, lnLastCol).find(something))

.Range(A1, Result P1) 'So you are getting combined range which is $P$1:$AI$1

.Cells(1, lnLastCol).find(something) 'This will give you P1

推荐阅读