For Each Cell in Excel Macros

Question

Hello I am facing some issue while running a macro in Excel VBA.

For Each cell In [Data!A2.A65536]

I was using the above code, and was getting type Mismatch error. When I replaced it to

For Each cell In Worksheets("Data").Range("A2:A65536").Cells

the error was gone.

Since I am not well versed in vba, can anyone explain what's the difference in the above snippets? What was the issue due to which the aforementioned issue was happening? and can someone please confirm, if I have correctly replaced the code.

Answer 1

The second one is "best practice" and is the one you should use.

The square brackets is just a shortcut notation for the range property (see Refer to Cells by Using Shortcut Notation) it is actually the same as using the Evaluate Method.

You get an error because you used a dot . instead of : in [Data!A2.A65536] it should be [Data!A2:A65536].

So using [Data!A2:A65536] is the same as Evaluate("Data!A2:A65536"). If possible I would avoid Evaluate or the bracket notation.