vba - 循环错误 - 运行时错误 13

Question

我有一个循环返回运行时错误 13，但我不知道为什么。我已将整个列更改为文本，但它仍然不起作用 - 有人知道为什么吗？ myColumn 中有字符串“Test”。返回错误的行是以 If Left(

Option Explicit

Sub myMacro()

Dim startPoint As range
Dim myCell As range
Dim columnToFind As Integer
Dim i As Integer
Dim myColumn As range

Application.ScreenUpdating = False

Worksheets("mySheet").Activate

Dim myDate As String                            

myDate = Application.InputBox("Please enter the date you would like to run 
the macro for (DD/MM/YY)")



Rows("4:4").Select
Selection.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

Set startPoint= ActiveCell.Offset(1, 1)

ActiveCell.EntireColumn.Offset(0, 2).Insert

columnToFind = Application.WorksheetFunction.Match(myDate, Rows("4:4"), 0) - 
1


i = startPoint.CurrentRegion.Columns.Count

Set myColumn = startPoint.Offset(0, (-i) + 1).EntireColumn


For Each myCell In myColumn

    If Left(myCell.Value, 4) = "Test" Then

        myCell.Offset(0, 1).Value = "testing"

    End If

Next

Application.ScreenUpdating = True

End Sub

Answer 1

问题是 myCell 正在循环列，因为它是用 .EntireColumn 声明的。因此，如果您编写 Debug.Print myCell.Address，它将显示整个列 - 例如G:G。并且整列没有.Value，因此返回错误。

尝试像这样循环:For Each myCell in myColumn.Cells:

Sub TestMe()

    Dim myColumn As Range
    Dim myCell As Range
    Dim i As Long: i = 5
    Dim yesterday As Range: Set yesterday = Range("K10")

    Set myColumn = yesterday.Offset(0, (-i) + 1).EntireColumn
    For Each myCell In myColumn.Cells
        If Left(myCell.Value2, 4) = "Test" Then
            myCell.Offset(0, 1) = "testing"
        End If
    Next

End Sub