CODEWITHSUNDEEP
vbaexcel
Gandalfrandalf
Gandalfrandalf

Reputation: 257

loop error - Runtime error 13

I have a loop that returns a runtime error 13 and I'm not sure why. I have changed the whole column to text and it still does not work - anyone have any idea why? There is the string "Test" in myColumn. The line that returns the error is the one beginning 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

Upvotes: 3

Views: 82

Answers (1)

Vityata
Vityata

Reputation: 43575

The problem is that myCell is looping through columns, because it is declared with .EntireColumn. Thus, if you write Debug.Print myCell.Address, it will display the whole column - e.g. G:G. And the whole column does not have .Value, thus it returns an error.

Try to loop like this: 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

Upvotes: 5

Related Questions