CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 1842

looping through a range of cells

I need to run the function AddColumnofData on each cell from c27 and every contiguous non empty cell to the right, but I get a "Run-time error 424 object required", any help woul dbe greatly appreciated.

Set col = Range("$C$27", Range("$C$27").End(xlToRight))

For Each c In col
    AddColumnofData (c)
Next c

Upvotes: 1

Views: 9048

Answers (3)

Eric
Eric

Reputation: 181

You can declare or set your range objects before passing them to your function. To prove that you are passing the correct values to your function, try this.

Dim r As Range '-- if you don't declare it as a range type you get a variant type as default
Dim c As Range '-- this is used to store the single cell in the For Each loop

Set r = Range("A1:D1") '-- substitute your range as per your example

For Each c In r '-- you could also use r.cells

    MsgBox c.Value '-- pass to your function instead of a call to the Message Box

Next

This produces a series of 4 Message Boxes containing the values in cells A1 to D1 of the active worksheet, if your Range "r" is seriously large then pass it to Debug.Print instead.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

On the assumption you have defined AddColumnofData as

Sub AddColumnofData(c As Range)
    ...
End Sub

your call to it needs to be 

AddColumnofData c

(that is, remove the brackets)

Jesse's advice to DIM your variables, while not not manditory is good advice. Applies to col as well. To make it manditory add Option Explicit to the top of your module.

Upvotes: 3

Jesse
Jesse

Reputation: 1935

If you declare c:

Dim c as Range

Then what you have should work.

Upvotes: 0

Related Questions