vba - Excel VBA查找地址并分配给变量然后重用值

Question

我试图查找单元格上是否有给定的标题，将该单元格的地址传递给变量并使用这样的位置来调整列的大小。我这样做的原因是因为我正在编写几个可以移动列位置的函数。如果有人能看一下并告诉我我做错了什么，我将不胜感激。

Option Explicit

Sub adjustColumns()

Dim PONumberCell As String
Dim PONumberAddress As Range
Dim TopLabelinColumn As Range

For Each TopLabelinColumn In Range("A1:Z1").Cells
    If TopLabelinColumn Like "PO_NUMBER" Then TopLabelinColumn.Value = "PO"
    PONumberCell = TopLabelinColumn.Address

    Set PONumberAddress = PONumberCell
    PONumberAddress.ColumnWidth = 70

Next TopLabelinColumn

End Sub

Answer 1

根据OP的进一步请求进行编辑:

您将 Range 对象(如 PONumberCell 的本义)与 String 变量一(如 PONumberAddress )混淆，因此

Set PONumberAddress = PONumberCell

不起作用，因为您试图将对象变量分配给字符串一个

但是您可以更有效地避免循环并使用 Find() 方法

Option Explicit

Sub adjustColumns()
    Dim PONumberAddress As String
    Dim PONumberCell As Range

    Set PONumberCell = Range("A1:Z1").Find(what:="PO_NUMBER", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
    If Not PONumberCell Is Nothing Then
        With PONumberCell
            .value = "PO"
            PONumberAddress = .Address
            .EntireColumn.ColumnWidth = 70
        End With
    Else
        Set PONumberCell = Range("A1:Z1").Find(what:="PO", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| if it didn't find "PO_NUMBER" then it seaches for a complete match of "PO"
        If Not PONumberCell Is Nothing Then PONumberCell.EntireColumn.ColumnWidth = 70
    End If
End Sub