时间戳

首页 > 解决方案 > 当我尝试循环遍历一系列单元格时，我不断收到“类型不匹配”错误

excel - 当我尝试循环遍历一系列单元格时，我不断收到“类型不匹配”错误

问题描述

B5:P5如果单元格中的标题包含特定文本，我想删除整个列。

这是我到目前为止的代码：

Sub Make_Prod()

Sheets("4. Partic Charges - Final").Select
Dim i As Long
Dim StartCell As Range
Dim EndCell As Range
Dim MyValue As Range
Set StartCell = Range("B5")
Set EndCell = StartCell.End(xlToRight)

'Delete IID, LastName, FirstName, SSN, DOB, DOT, VestBal, TotalSourceDH, HasCovg, and any blank headers with values in column

For i = StartCell To EndCell
    MyValue = Range(i & 5)
        If MyValue = "IID" _
        Or MyValue = "LastName" _
        Or MyValue = "FirstName" _
        Or MyValue = "DOB" _
        Or MyValue = "DOT" _
        Or MyValue = "HasCovg" _
        Or MyValue = "" _
    Then Range(5 & i).EntireColumn.Delete

Next i
    
End Sub

每次我尝试运行代码时，我都会得到一个

类型不匹配

i = StartCell to EndCell我的陈述有误。

谁能帮我这个？

标签： excelvbaloops

解决方案

您的错误的直接原因是 Nathan_Sav 指出您正在尝试使用数字循环遍历一个范围。

尝试这个。当您删除列时，您需要向后迭代以避免跳过单元格。设置范围（您可以在一行中完成），然后使用计数器变量循环遍历每个单元格。

你不需要Select Case，你可以坚持，If但在我看来更清楚一点。

Sub Make_Prod()

Sheets("4. Partic Charges - Final").Select
Dim i As Long
Dim StartCell As Range
Dim EndCell As Range
Dim MyValue As Range
Set StartCell = Range("B5")
Set EndCell = StartCell.End(xlToRight)

'Delete IID, LastName, FirstName, SSN, DOB, DOT, VestBal, TotalSourceDH, HasCovg, and any blank headers with values in column
Dim r As Range, c As Long

Set r = Range(StartCell, EndCell)

For c = r.Count To 1 Step -1
    Select Case r.Cells(c).Value
         Case "IID", "LastName", "FirstName", "SSN", "DOB", "DOT", "VestBal", "HasCovg", ""
            r.Cells(c).EntireColumn.Delete
    End Select
Next c

End Sub

推荐阅读