插入新的表列以包含以前列的IFELSE结果

Question

​

​

列L包含精品编号和AB列，其中新插入的列包含精品化代码。

正如我的标题所建议的，我希望在表中添加一个新列，其中包含前一列中的其他值( L列，其中包含A、B、C、D、E等值，具体取决于精品店)。我的代码在下面

Sub BoutiqueCodes()
Dim tbl As ListObject
Dim cel As Range
Dim boutique As String[enter image description here][1]
Dim codes As Integer
boutique = Range("L, L").Value
'the boutique values come from Column L
    Set tbl = ActiveSheet.ListObjects("Table_1")
    With tbl
        .ListColumns.Add.Name = "Code"
        For Each cel In .ListColumns("Code").DataBodyRange.Cells
            If boutique = "A" Then
                codes = 506
             ElseIf boutique = "B" Then
                codes = 606
            ElseIf boutique = "C" Then
                codes = 706
            ElseIf boutique = "D" Then
                codes = 611
            ElseIf boutique = "E" Then
                codes = 612
            Else
                codes = 0
            End If
            cel.Value = codes
        Next
    End With
End Sub

我的boutique = Range("L, L").Value线路出了问题。我确信这一点。我在新的专栏里想要的是精品店的相应代码。以前，我没有boutique = Range("L, L").Value行，但是我的所有代码值都是0。任何帮助都会很好。谢谢

Answer 1

用计算将列添加到表中

使用Offset快速修复

Option Explicit

Sub BoutiqueCodes()
    Dim tbl As ListObject
    Dim cel As Range
    Dim boutique As String
    Dim codes As Long
    Dim ColumnOffset As Long
    Set tbl = ActiveSheet.ListObjects("Table_1")
    With tbl
        .ListColumns.Add.Name = "Code"
        ColumnOffset = .ListColumns("Boutique").Index _
                     - .ListColumns("Code").Index
        For Each cel In .ListColumns("Code").DataBodyRange.Cells
            boutique = cel.Offset(, ColumnOffset).Value
            If boutique = "A" Then
                codes = 506
            ElseIf boutique = "B" Then
                codes = 606
            ElseIf boutique = "C" Then
                codes = 706
            ElseIf boutique = "D" Then
                codes = 611
            ElseIf boutique = "E" Then
                codes = 612
            Else
                codes = 0
            End If
            cel.Value = codes
        Next
    End With
End Sub