Question posted 2013 · +5 upvotes
Why does the following not work:
Range(Cells(1,1)).Value = 3
Cells(1,1) should essentially be the same thing as using A1 right?
(I realize that I could just do Cells(1,1).Value = 3, but I’m just curious as to why it doesn’t work.)
I read the MSDN entry and it shows that the first argument must be A1 style, yet something like this does work:
Range(Cells(1,1), Cells(2,3)).Value = 2
Totally confused.
Accepted answer +9 upvotes
When Range is used with a single parameter, the parameter is is interpreted as a range name.
Range(Cells(1,1))
is the same as using
Range(Cells(1,1).Value)
So you will get a result only is the value of Cells(1,1) is a valid range address in A1 style
Only when passed two range parameters are they interpreted as the corners of a range.
2 code variants in this answer
- Variant 1 — 1 lines, starts with
Range(Cells(1,1)) - Variant 2 — 1 lines, starts with
Range(Cells(1,1).Value)
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.