比较一列的文本以确定另一列的输出

Question

好吧。这就是我在A1:A 100栏中的内容：

12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
12V Automotive Products                             
A/V Cables                                          
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Accessories                                         
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action                                              
Action & Adventure                                  
Action & Adventure                                  
Adapters                                            
Adapters                                            
Adapters                                            
Adapters                                            
Adapters & Splitters                                
Adapters & Splitters                                
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           
Adventure                                           

这是密码：

Sub FillColumnB()
Dim rng As Range, cl As Range

    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For Each cl In rng

    If cl = "12V Automotive Products" Then
    cl.Offset(0, 1) = "tdexjxr"
          ElseIf cl = "Accessories" Then
          cl.Offset(0, 1) = "s6ii"
    ElseIf cl = "Action" Then
    cl.Offset(0, 1) = "7ks57k5k"
    ElseIf cl = "Action & Adventure" Then
    cl.Offset(0, 1) = "kxee5xskex"
    ElseIf cl = "Adapters" Then
    cl.Offset(0, 1) = "kxykk5ezw"
    ElseIf cl = "Adobe Titles" Then
    cl.Offset(0, 1) = "kz46yk78"
    ElseIf cl = "Adventure" Then
    cl.Offset(0, 1) = "l8rrzlez"
    ElseIf cl = "All Toys" Then
    cl.Offset(0, 1) = "ezlllels6"
    ElseIf cl = "Animation" Then
    cl.Offset(0, 1) = "988l7889l"
    ElseIf cl = "Anti-Virus/Anti-Spyware" Then
    cl.Offset(0, 1) = "wq3w"
    ElseIf cl = "Applications" Then
    cl.Offset(0, 1) = "jrd5j"
    ElseIf cl = "Arcade" Then
    cl.Offset(0, 1) = "drj76j"
    ElseIf cl = "Arts & Humanities" Then
    cl.Offset(0, 1) = "8l"

        End If
    Next
    End Sub

我的问题是，为什么上面的代码不能工作？

Answer 1

首先，Select Case块比一系列ElseIf语句更有意义。至于为什么它“不工作”没有错误，有两个可能的问题：

1)您没有访问单元格对象的值。它应该是隐式的，但是指定它应该会有帮助。

2)您没有处理单元格的值与列出的任何文本不匹配的情况。添加最后一个Else案例应该可以处理这种可能性。如果rng中的所有单元格都是这样的话，那么更多地查看它们的内容。也许有前导或尾随的空白需要移除。

For Each cl In rng.cells

    Dim outCell as Range
    Set outCell = cl.offset(0,1)

    Select Case cl.value
        Case "12V Automotive Products"
            outCell.value = "s6ii"
        Case "Action"
            outCell.value = "7ks57k5k"
        'Case ...
        '    outCell.value = ...
        Case Else
            outCell.value = "Not Recognized Value"
    End Select

Next cl

Answer 2

您需要确保您的范围正在获取所有信息，因为正如您提到的，您的代码适用于较小数量的数据(10行)。使用下面的内容显示一个消息框，通知您范围选择中的行总数。确保它正在检查正确的行数：

    Dim rng As Range, cl As Range
Dim rngCheck As Integer

    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    rngCheck = Range("A" & Rows.Count).End(xlUp).Row
    MsgBox rngCheck

从上一页复制到新页：

Sheets("Name of Sheet").Range(Range of Cells).Copy
Sheets("Destination Sheet").Range(Where you want it).PasteSpecial Paste:=xlPasteValues