使用Excel VBA搜索列标题并插入新列

Question

我是一名有用的助手，可以为您翻译文本。

我有一个经常更新的电子表格，因此列标题位置经常更改。例如，今天“用户名”是K列，但明天“用户名”可能是L列。我需要在“用户名”的右侧添加一个新列，但当它更改时，我不能引用单元格/列引用。

到目前为止，我已经：

Dim rngUsernameHeader As Range
Dim rngHeaders As Range
Set rngHeaders = Range("1:1") 'Looks in entire first row.
Set rngUsernameHeader = rngHeaders.Find("Username")

当我想在其右侧添加新列时，我选择了该行，但它回到了单元格/列引用...

Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveCell.FormulaR1C1 = "Role"

我该如何使用宏执行此步骤？

编辑：我认为需要给该列一个标题名称，并开始填充行数据 - 每次开始时都会引用单元格，我希望尽可能避免这种情况。

非常感谢。

- Graham McCubbin

---

问题不清楚是什么？你只是想在叫做“username”的列后面插入一个叫做“role”的列吗？ - Alex P

是的，并使用vlookup填充该列，直到数据的最底行。 - Graham McCubbin

Answer 1

怎么样：

Sub qwerty()
    Dim rngUsernameHeader As Range
    Dim rngHeaders As Range

    Set rngHeaders = Range("1:1") 'Looks in entire first row.
    Set rngUsernameHeader = rngHeaders.Find(what:="Username", After:=Cells(1, 1))

    rngUsernameHeader.Offset(0, 1).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 1).Value = "role"
End Sub

- Gary's Student

---

完美运行。那么，我如何进入此行的第二行（暂时假设为列L）以输入公式，而不使用特定单元格引用？ - Graham McCubbin

如果 rngUsernameHeader.Offset(0, 1) 在第一行，那么 rngUsernameHeader.Offset(1, 1) 就在第二行！ - Gary's Student

再次感谢。我已经得到了以下内容： rngUsernameHeader.Offset(0, 1).Value = "角色" rngUsernameHeader.offet(1, 1).Value = "=VLOOKUP(RC[-1],Role!C[-10]:C[-9],2,0)"不确定如何定义范围以自动填充公式至数据的最底行... Selection.autofull Destination:=Range - Graham McCubbin

Answer 2

Sub AddColumn
    Dim cl as Range

    For each cl in Range("1:1")
        If cl = "username" Then
           cl.EntireColumn.Insert Shift:= xlToRight
        End If

        cl.Offset(0, 1) = "role"
    Next cl
End Sub

未经测试的代码，因为不在我的桌面电脑上。

- Alex P

---

在这一行代码上出现了语法错误："For each cl in Range (1:1)"。 - Graham McCubbin

@Vityata - 感谢您编辑我的代码以修复语法错误 :-) - Alex P

Answer 3

应该可以这样做。思路是先定位列，然后向右插入。这就是为什么在TestMe中有+1的原因。函数l_locate_value_col返回找到值的列。如果需要，您可以更改可选参数l_row，具体取决于您要查找哪一行。

Option Explicit

Public Sub TestMe()

    Dim lngColumn         As Long

    lngColumn = l_locate_value_col("Username", ActiveSheet)
    Cells(1, lngColumn + 1).EntireColumn.Insert

End Sub

Public Function l_locate_value_col(target As String, _
                                    ByRef target_sheet As Worksheet, _
                                    Optional l_row As Long = 1)

    Dim cell_to_find                As Range
    Dim r_local_range               As Range
    Dim my_cell                     As Range

    Set r_local_range = target_sheet.Range(target_sheet.Cells(l_row, 1), target_sheet.Cells(l_row, Columns.Count))

    For Each my_cell In r_local_range
        If target = Trim(my_cell) Then
            l_locate_value_col = my_cell.Column
            Exit Function
        End If
    Next my_cell

    l_locate_value_col = -1

End Function

- Vityata

Answer 4

你可以给你的范围命名：

Sub Test()

    Dim rngUsernameHeader As Range
    'UserName is in column F at the moment.
    Set rngUsernameHeader = Range("UserName")
    Debug.Print rngUsernameHeader.Address 'Returns $F$1
    ThisWorkbook.Worksheets("Sheet2").Range("E:E").Insert Shift:=xlToRight
    Debug.Print rngUsernameHeader.Address 'Returns $G$1

End Sub

编辑： 已重写代码，使其在您指定的列后插入一列并返回该引用：

Sub Test()

    Dim rngUsernameHeader As Range
    Dim rngMyNewColumn As Range

    Set rngUsernameHeader = Range("UserName")
    rngUsernameHeader.Offset(, 1).Insert Shift:=xlToRight

    'You'll need to check the named range doesn't exist first.
    ThisWorkbook.Names.Add Name:="MyNewRange", _
        RefersTo:="='" & rngUsernameHeader.Parent.Name & "'!" & _
                         rngUsernameHeader.Offset(, 1).Address

    Set rngMyNewColumn = Range("MyNewRange")
    MsgBox rngMyNewColumn.Address

End Sub

- Darren Bartrup-Cook