使用Excel VBA搜索列标题并插入新列 [英] Search column headers and insert new column using Excel VBA

问题描述

我有一个定期更新的电子表格.因此，列标题位置会定期更改.例如.今天用户名"是K列，但是明天用户名"可能是L列.我需要在用户名"的右边添加一个新列，但更改之处我不能称为单元格/列引用.

I have a spreadsheet that is updated regularly. Therefore the column header positions change regularly. eg. today "Username" is column K, but tomorrow "Username" might be column L. I need to add a new column to the right of "Username" but where it changes I cannot refer to as cell/column reference.

到目前为止，我有:

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

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

When I go to add a new column to the right of it, I'm selecting that row but it's going back to cell/column references...

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

如何使用宏执行此步骤?

How can I perform this step with a macro?

我认为需要给Column一个标题名称，并开始用数据填充行-每次我开始时都希望避免的单元格引用.

edit: I think need to give that Column a header name and begin populating the row with data - each time I do begins the cell references which I want to avoid wherever possible.

非常感谢.

推荐答案

怎么样:

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

这篇关于使用Excel VBA搜索列标题并插入新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！