在VBA中创建列免疫引用？

Question

我正在处理一个项目，在该项目中，所有行和许多列都要检查多个条件。问题是从工作表中添加/删除了列，并且在目前，这会导致我的所有单元格(行、列)引用都是off +输出不正确的信息。我想知道是否有一种方法可以使我的列引用更加健壮，以便它们能够自动找到正确的标头，并在检查时使用它们？这个问题的解决方案是否能够解释包含完全相同的标题文本的多列？

基本上：

1. 无空白栏
2. 列标题有重复(例如，第1列标题："Financials"；第15栏标题："Financials")
3. 列是基于从工作表中添加/删除列而向右和向左移动的。

请在下面找到我当前代码的一个简短示例，并附上说明：

Dim i As Integer
Dim lastRow As Long
Dim lastCol As Long

lastRow = Range("A1").End(xlDown).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To lastRow
    Select Case Cells(i, 14).Value
        Case Is = "Yes"
            Select Case True
                Case Cells(i, 63).Value = 6 And _
                    (IsEmpty(Cells(i, 77)) Or IsEmpty(Cells(i, 93)) Or IsEmpty(Cells(i, 109)) Or _
                    IsEmpty(Cells(i, 125)) Or IsEmpty(Cells(i, 141)) Or IsEmpty(Cells(i, 157))) 
                    Cells(i, 174).Value = "True" '^THESE CELL VALUES ALL HAVE THE SAME COLUMN HEADER TITLE

Answer 1

如果表是一致的-从A1开始并占据一个连续块-那么Range("A1").CurrentRegion将引用该表。

然后，您可以使用.CreateNames根据列的标题来命名列(即使用命名范围)。

Dim rngTable As Range
Dim rng As Range

Set rngTable = Range("A1").CurrentRegion

rngTable.CreateNames True, False, False, False
' that is, based on the first row headings

Range("Salary").Select 'prove it works

'if necessary, iterate the cells of the column,
For Each rng In Range("Salary")
    rng.Value = rng.Value + 10
Next 'rng

但是，如果一个列标题被重复("Financial")，那么您将被要求确认，第二个出现将推翻第一个。(或者你可以说“不”，第一次出现就会被命名。)在这种情况下，最好首先纠正这些重复的标题。

纠正重复的标题不一定是直截了当的，但无论如何，你应该解决一些问题。如果它是一个特定的词"Financials“(或单词)可以被复制，那么这将使任务变得更容易。您可以数一数发生了多少次，并将第二次更正为"Financials2“。

Answer 2

一种向列分配Name的简单方法。假设列N有标题"Payments“。首先，将名称“支付”指定给该列：

​

​

然后在VBA中，我们可以编写如下代码：

Sub dural()
    Dim rng As Range, colly As Long
    Set rng = Range("Payments")
    colly = rng.Column
    For i = 2 To 100
        If Cells(i, colly) = "whatever" Then
            MsgBox "Help"
        End If
    Next i
End Sub

即使您在列N之前添加/删除列，代码也将继续工作。