excel - VBA宏:公式基于更改位置的列

我试图调整我的宏，以便它在始终更改位置的特定列旁边创建一个列。在下面的宏中，它只是左侧6列的绝对引用。但是，情况并非总是如此。我是否应该通过在第一行中找到列名来进行设置？

基本上，宏会创建一个新列，如果它是重复的，则会放入IF语句，然后设置条件格式以突出显示“ 1”的所有值。对不起，如果我没有清楚地说明这一点！

Sub test()
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=R[-1]C[-6],R[-1]C+1,1)"
    Range("L2").Select
    Selection.Copy
    Range("K2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

最佳答案

我为此有一个工作代码，但它要求您的数据在表中。这是动态操纵和引用数据（列，行等）的最佳方法。

另外，我严重依赖ListObject方法。它确实可以很好地处理表格。

Public Sub InsertColumn(Optional columnName As String, Optional BeforeORAfter As String)
Dim loTableName As ListObject
Dim loColumn As ListColumn
Dim newColDest As Long

'Handles user input if they desire the column inserted before or after
Select Case UCase(BeforeORAfter)
    Case Is = "BEFORE"
        newColDest = 0  'Inserts column and moves reference column right
    Case Else
        newColDest = 1  'Inserts column to the right of reference column
End Select

'Ensures the user selects a reference column name
Select Case columnName
    Case Is = ""
        columnName = InputBox("Enter column name to be referenced.", "Enter Column Name")
    Case Else
End Select

'Sets the ListObject as the table.
Set loTableName = Range("TableName").ListObject

With loTableName
    On Error GoTo InsertError   'Exits sub in case the column couldn't be found
    .ListColumns.Add (.ListColumns(columnName).Index + newColDest)
End With
Exit Sub

InsertError:
    'Most likely error is user typed the column header incorrectly.
    MsgBox "Error creating column. Ensure a correct reference column was chosen", vbExclamation + vbOKOnly, "Insert Error"

End Sub

有任何疑问或问题，请告诉我。