Compile error when running a for loop over a range

Question

I have the following code that tries to get a dynamic range and print the values of each cells of the range.

Sub Macro3()

'
    Range("D10").Select
    x = Range(Selection, Selection.End(xlDown)).Select


    For Each cell In x
        MsgBox (cell)
    Next x

End Sub

If I run it however it get a compile error... Any thoughts on what goes wrong here?

Inaccurate reference to variable next (translated from Dutch) — Frits Verstraten
– Frits Verstraten, Commented Jul 12, 2016 at 10:26

Fratyx · Accepted Answer · 2016-07-12 10:27:23Z

1

Try this: (use Set and remove .Select)

Set x = Range(Selection, Selection.End(xlDown))

and Next or Next cell instead of Next x

answered Jul 12, 2016 at 10:27

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2016-07-12 10:30:44Z

0

Option Explicit

Sub Macro3()

    Dim x       As Range
    Dim cell    As Range


    Range("D10").Select

    Set x = Range(Selection, Selection.End(xlDown)).Select
    For Each cell In x
        MsgBox (cell)
    Next cell

End Sub

Hi. you need next cell and not next x. Enjoy! :D

answered Jul 12, 2016 at 10:30

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Compile error when running a for loop over a range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related