CODEWITHSUNDEEP
vbaexcel
Wilhelm Sorban
Wilhelm Sorban

Reputation: 1131

Object required error when looping through cells in selection

I am looping through some cells, in a vertical selection, in Excel, and then passing that cell as a parameter to a procedure.

I have done it this way, so I don't have the contents of ProcessCells twice, in the code, once for the while loop, and the second time in the For loop.

If I try and get the value of the cell written out, in the for loop, it works. If I put the contents of the ProcessCells procedure in the for loop, it also works.

But if I try to pass it as a parameter, into ProcessCells, I am getting an error

'Object Required'

Here is the code, if you want to check it out:

Sub loopThroughCells()

Dim c As Range
Dim autoSelect As String
Dim X, Y As Integer

autoSelect = Cells(3, 2).Value

If StrComp(autoSelect, "Y") = 0 Then
    Y = 5
    X = 4
    While Not IsEmpty(Cells(Y, X).Value)
        ProcessCells (Cells(Y, X))
        Y = Y + 1
    Wend
Else
    For Each c In Selection
        ProcessCells (c)
    Next c
End If
End Sub

Sub ProcessCells(ce As Range)
End Sub

How is

Cells(n,m)

different from

c In Selection

?

The error happens in the For loop, but it doesn't happen in the while loop.

Upvotes: 2

Views: 841

Answers (1)

Vityata
Vityata

Reputation: 43585

Here is how you should do it:

Option Explicit

Sub TestMe()

    Dim c   As Range

    For Each c In Selection
        Call ProcessCells(c)
    Next c
End Sub


Sub ProcessCells(ce As Range)
End Sub

You should refer with call, because you have an argument in parenthesis. Or like this, if you do not like the call:

Option Explicit

Sub TestMe()

    Dim c   As Range

    For Each c In Selection
         ProcessCells c
    Next c
End Sub


Sub ProcessCells(ce As Range)
End Sub

Plus a small edition of your code. Make your declarations like this:

Dim X as Long, Y As long

In your code X is declared as a variant, and integer is slower and smaller than long - Why Use Integer Instead of Long?

Here is some good explanation when to put the argument in parenthesis and when to use the call - How do I call a VBA Function into a Sub Procedure

Upvotes: 3

Related Questions