Learing about Range Objects

Playing around trying to learn Range Objects (still having a hard time learning VBA stuff lol)

I can input data into cells on active WS using this...

Range("a1") = 6

and this...
Range("J1:L4") = 8
and this...

Range(Cells(2, 2), Cells(2, 2)) = 7

Then I started putting data into a different sheet...

Sheets("Sheet2").Range("A1") = "ccc"

These all worked as expected.

Then I tried to use the Cells() command on a different sheet and got an error message

Sheets("Sheet2").Range(Cells(2, 2), cells(4,4) = 50

I get a syntax error

What am I doing wrong?

Hi there,

I think you're suffering from "parenthesis imbalance syndrome"

Sheets("Sheet2").Range(Cells(2, 2), cells(4,4)) = 50

Also, rather than use the default property (Value) of a range object, it might be better to specify it explicitly, i.e.

Sheets("Sheet2").Range(Cells(2, 2), cells(4,4)).Value = 50

Hope this helps,

Regards,

Greg M

I meant to add (ok ok, rookie poster error lol) that the active sheet is sheet1

Ok, so why does this work...

Sheets("Sheet2").Range("A1") = "ccc"

and this does not...

Sheets("Sheet2").Range(Cells(2, 2), Cells(4, 4)) = 50

Greg, thanks for the tip, fixed the )

As far as using .value, I changed to "aa" and it still didn't work
The only difference is that I used a different reference method

I get an error saying "application defined or Object defined error"

Originally Posted by Greg M

Hi again,

The Cells property, when used with an unqualified reference, always applies to the active worksheet, so your code is like writing:

Sorry about the knee-jerk reaction to your previous post - when I spotted the missing parenthesis I didn't look any further

Regards,

Greg M

OK, thanks, that makes sense to me now. I am trying to learn from some on-line courses, and maybe Im jumping ahead of myself (them)

The missing ")" was a very valid observation, I had looked for that, but missed it, so thanks

Originally Posted by Greg M

The Cells property, when used with an unqualified reference, always applies to the active worksheet

Not quite. As Jindon said, if the code is in a worksheet code module, it would apply to that sheet, regardless of whether it's active or not.

.

Originally Posted by FDibbins

......
and this does not work ...

Sheets("Sheet2").Range(Cells(2, 2), Cells(4, 4)) = 50

......

....wot Romper and Jindon said, I learnt the hard way like you just did, but...

. - To do it in a similar way to wot you want, this works

Sheets("Sheet2").Range("B2", "D4") = 50

.. and you can combine the two, which i have found useful if for example with the second cell you need to use the cells bit, when, for example, you must use a variable for the last column. - If you know the first cell is fixed you can simplify half of it .

 lc = 4
Sheets("Sheet2").Range("B2", Sheets("Sheet2").Cells(4, lc)) = 50

.. and you probably know, or can guess, if the last row is the issue with the need to use a variable, then it stays simple

 Let lr = 4
Sheets("Sheet2").Range("B2", "D" & lr & "") = 50

. Alan

Edit:
P.s. you could check this out to get all that stuff a bit clearer:
http://excelmatters.com/referring-to-ranges-in-vba/

Originally Posted by romperstomper

Not quite. As Jindon said, if the code is in a worksheet code module, it would apply to that sheet, regardless of whether it's active or not.

….. and a further point ( from you )

Originally Posted by RoryA

…. In a normal module, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.

.. which usually but not always is the Active Sheet
http://www.mrexcel.com/forum/excel-q...ml#post4038308

Hi again,

Many thanks for your feedback - glad to have been able to help.

Best regards,

Greg M


P. S. Thanks for the Reputation increase - much appreciated

Hi Ford,

Is all your above code behind a sheet or in a Module? This might help understand why it is important.
http://www.cpearson.com/excel/Scope.aspx

Here might be some more help with your VBA Ranges.
http://www.anthony-vba.kefra.com/vba/vbabasic2.htm

Marvin, it is in Module1 (if that helps). It was just a lesson I was young through from here...
http://wellsr.com/vba/excel/
chapt 2.1

Then I just tried messing around with "what if" stuff. Don't forget, I am a total noob/dummy with VBA lol

Hey Ford,

A few years ago I stumbled onto LOTS of different ways to select a range of cells using VBA. I realized some were more useful than others for looping or even reading my own code. I ended up using Cells more than Range type of VBA, IE
Range("D7") was the same as Cells(7,"D"). This is the same as Cells(7,4) as "D" is the 4th column. When I got over to the "P"th column numbers didn't allow me to see the letter and I got confused so I liked Cells(4,"P") better than Cells(4,16).

Then I needed to deal with stuff like Range("B4:F7") and how to work with this using my Cells notation. I discovered I could write
Range(Cells(4,"B"),Cells(7,"F")) and this seemed to work.

When I write code I use the Immediate window and try to see if VBA will like my notation. This keeps me humble when it doesn't know what I mean.

Here is another great site to look at when dealing with selecting ranges of cells.
http://www.bettersolutions.com/excel...A621561212.htm

Here are some things to try. Name a range of cells, like "TestRange" Then in the Immediate Window try these

Range("TestRange").Select ' see if it selects your named range
' The Question Mark as the first letter in the Immediate Window means "Debug.Print"
? Range("TestRange").Address ' does this give the address of your named range?
?range("TestRange").Rows.Count
?range("TestRange").columns.Count
?range("TestRange").cells.Count
?range(cells(2,"B"),cells(4,"F")).cells.Count
Now for some fun.... Start typing (in the immediate window)
?range("TestRange"). ' when you type the period look at all the possible stuff that is available!!!

Try each of these and find out what they do... Like .Calculate?? You mean you can have VBA calculate a range of cells and not the whole worksheet/workbook? YEP!!

Try to understand all the different words that can come after the period and this is where you will become an expert with VBA and Object Based languages.

Thanks for the assist and advice guys, really appreciated - now, all I have to do is try and remember this stuff

I have been taking memory pills to try and help my failing memory...i keep forgetting to take them though

@ MarvinP

Originally Posted by MarvinP

............................
?range("TestRange"). ' when you type the period look at all the possible stuff that is available!!!
..........................................
Try to understand all the different words that can come after the period and this is where you will become an expert with VBA and Object Based languages.

…. You bet – I have been learning for about a year and am still amazed how many “things” can be “got at” after hitting .Dot after the Range Object!!

@ MarvinP

Originally Posted by MarvinP

..... I discovered I could write
Range(Cells(4,"B"),Cells(7,"F")) and this seemed to work........

…. That possibility of combining Letters and number conventions was along my Post #12 and a point worth emphasizing… ( P.s you are just doing a quick demo there so have missed out the often importand worksheet qualifier I guess: ws.Range(ws.Cells(4,"B"),ws.Cells(7,"F")) )
………………………………………………………………………………

@ FDibbins

Originally Posted by FDibbins

Thanks for the assist and advice guys, really appreciated - now, all I have to do is try and remember this stuff…..

. It is a bit of an honor to help you. If I can indulge a last time, and try to keep it simple as I can…
. 1 ) in any spare or new file put some stuff, formatting etc in first cell, such as…

Using Excel 2007

Row\Col	A
1	anyting

. 1 a) Make sure your file is saved ( so resave it ) as “With macros” ( .xlsm )
. 2 ) click Alt + F11
. 2a) A big Module Window should should come up ( If not select .. Insert … Module )
. 3) copy and paste in that window the following code

Option Explicit
Sub RangeObject()
10 'Range Object Method, Properties etc. Demo
20 Dim rng As Range
30 Set rng = ThisWorkbook.Worksheets.Item(1).Range("A1") 'Refers to A1 in the First Sheet ( That is the first left Tab Sheet)
40 'Click in the left Margin parallel to End Sub at the end of this code. End Sub Turns Brown/Orange and a Brown/Orange circle appears wher you clicked
50 'Then run the code. ( Click anywhere in this code and hit F5 ). End Sub turns Yellow, indicating that the code has paused
60 'Highlight either of the two rng above. Hit Shift + F9. Click Add ( or sililar ). You get The Range Object shown in the "Watch Window"
70 'Click on the + Box, - then some of the others + boxes - it never seems to end !!!!!!
End Sub

.4 ) 'Click in the left Margin parallel to End Sub at the end of this code. End Sub should turn Brown/Orange and a Brown/Orange circle appears where you clicked
.5) 'Then run the code. ( Click anywhere in this code and hit F5 ). End Sub turns Yellow, indicating that the code has paused
.6) 'Highlight either of the two rng
.7 ) Hit Shift + F9. Click Add ( or similar ). You get, + rng , your Range Object , shown in what is the "Watch Window"
.8) Click on the + Box, - then some of the others + boxes - it never seems to end. !! For example you will find “anything” listed by Value2 ( which is the most fundamental underlying form of what is there, I think ) I have tried sometimes , to find other stuff, – it is all there , but can take a while!!!!!




……………………………………………………………………….

Originally Posted by FDibbins

…I have been taking memory pills to try and help my failing memory..

.. I sympathize, it is certainly very daunting. I have started a few “Learing about Range Objects” Threads” myself, here and elsewhere:
http://www.mrexcel.com/forum/excel-q...anomaly-2.html

.. maybe a last bit of mindless fun…. as I know you are an animal lover. Here is a real Donkey of an idea. Lets take the simple code Line to do what you want ( your original example code to pt 50 in B2 to D4 )

ws2.Range("B2:D4").Value = 50

. and let’s say we want to stay with this basic form, but we have the column numbers, 2 and 4 in variables, lB and lD rather than having the string letters B and D.
. We can get those string letters by a tedious bit of manipulation of the Cell Address for any cell in those columns, - Lets say in the first row Cells( 1, lB ) and cells( 1, lD )

Then we have a real Donkey of a code Line:

ws2.Range("" & Left(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), "$") - 1)) & "2:" & Left(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), "$") - 1)) & "4").Value = 50

. I hope you Don’t like it. I can't think of any reason you might want to use this insted of the endless other ways discussed. ( I would be worried for your sanity if you did like ! )
. I think it’s great. I will probably use it from now on in codes I do.

Alan

'

Sub BonkeyDonkeyFDibbins() 'http://www.excelforum.com/excel-programming-vba-macros/1102805-learing-about-range-objects.html
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Let ws2.Range("B2:D4").Value = 50
ws2.Range("B2:D4").ClearContents

'Variables for the column indices and the column Letters
Dim Bonkey As String, Donkey As String 'The Letters we want should go in 'ere
Dim lB As Long, lD As Long: Let lB = 2: Let lD = 4 'The indicies we have for the columns

'Get a Donkey
Let Donkey = ws2.Cells(1, lD).Address 'Returns $D$1
Let Donkey = Replace(Donkey, "$", "", , 1) 'Returns D$1
Let Donkey = Left(Donkey, (InStr(Donkey, "$") - 1)) 'Returns D
'or
Let Donkey = Left(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), "$") - 1))
'and similarly for a Bonkey
Let Bonkey = Left(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), "$") - 1))

'Donk in Bonk in
Let ws2.Range("" & Bonkey & "2:" & Donkey & "4").Value = 50
'or
Let ws2.Range("" & Left(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lB).Address, "$", "", , 1), "$") - 1)) & "2:" & Left(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), (InStr(Replace(ws2.Cells(1, lD).Address, "$", "", , 1), "$") - 1)) & "4").Value = 50
End Sub

FYI, there is no need for this:

Replace(ws2.Cells(1, lB).Address, "$", "", , 1)

The Address property has several arguments - the first two allow you to specify whether you want absolute row and/or column references. Nor is there any need to prepend "" to the column letter.

Originally Posted by romperstomper

…..
The Address property has several arguments - the first two allow you to specify whether you want absolute row and/or column references….

Ok , thanks, I must check that, may help to make the line a little less insane!!

. (This line actually came from a code line ( I do not think ) I could make much simpler ( accepting your address option argument would help a little )
. The line in question was how to get an Array ( 1 Dimensional “Pseudo horizontal “ ) of the column indicies for a continual range…( where I knew the start column as a letter A, but the stop column the code got in a long variable lc )

400 Dim clms() As Variant 'Array fo Indicies are obtained for the "columns" required in each "vertical" workbook is done just outside the loop as they are the same for all "Verticals". Variant type required to satisfy the type returned by the Evaluate method below
410 Let clms() = Evaluate("column(A:" & Left(Replace(Cells(1, UBound(arrin(), 2)).Address, "$", "", , 1), (InStr(Replace(Cells(1, UBound(arrin(), 2)).Address, "$", "", , 1), "$") - 1)) & ")") 'Not as complicated as it looks! - The messy bit just gets the Letter part out of the last cell Address. Then the worksheet Function Columns is used:  Returns a 1 D "quasi" horizontal array of size _: to :_  In that array are the number _:   to :_  .  This "quasi horizontal" Array we require for out "magic" code line   Dim arrOut() As Variant: Let arrOut() = Application.Index(arrin(), rws(), clms()) 'This "majic" I still do not understand  http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html

. Do you coincidentally have any suggestions on a simpler way THAT DOES NOT involve transpose ( I can do a transpose on an Evaluation of Rows rather than columns, but I am trying to reduce transposing.. ) ?

Originally Posted by romperstomper

…. Nor is there any need to prepend "" to the column letter.

.. Sorry did not quite follow you there, ( unless you are meaning that if I follow your suggestion and I pick the Address option which does not have $, then of course, I have no need to do a Replace thing. ( However it is convenient to have it – I take out the first and use the position of the second to assist getting to the Letter bit )

………………………………………………………
…. Incidentally getting the Column letter from the column number in a “one liner” seemed cool, - I had only Googled Fuktions people had done for it.

. But I guess you may know how to do that a lot neater?

Thanks again for the extra useful info.
Alan

You don't need "" & here.

Let ws2.Range("" & Left(...

Hi Norie,
.
. Thanks, I realize that, but I have found that always including it is a good habit as
.1 ) it sometimes helps when you are building long Evaluate(____) strings – it seems to make chopping out and pasting into it easier. Especially when you paste somewhere into the middle of a complicated Evaluate string, especially if there are a combination of spreadsheet and VBA code bits in there. It is only then at the end that you can finally miss you out.
.
. 2 ) And also I got caught out once not putting at least one of those “Quotes Ampersand Stuff” bits at doing something like this

Sub NorieQoutesAmbersandStuff()
'http://www.excelforum.com/excel-programming-vba-macros/1102805-learing-about-range-objects-2.html#post4183474
10  Dim ws1 As Worksheet, wb As workbook
20  Set wb = ThisWorkbook
30  Let wb.Worksheets.Item(1).Name = "Sheet1" 'Just in case it is not already Named Sheet1 - Will not error if it already is named Sheet1
40  Set ws1 = wb.Worksheets("Sheet1")
42  ws1.Range("A1").ClearContents
44  wb.Worksheets.Item(3).Range("A1").ClearContents
'
'Start of bit to demo getting caught out when not including at least 1 "Quotes Ampersand Stuff" bits
50  Dim FileNameAsANumber As Variant: Let FileNameAsANumber = 3 'Works to address Third item sheet in line 100
52  'Dim FileNameAsANumber As Variant: Let FileNameAsANumber = "3" 'Works to address First item sheet in line 100
54  'Dim FileNameAsANumber As String: Let FileNameAsANumber = 3 'Works to address First item  sheet in line 100
56  'Dim FileNameAsANumber As Variant: Let FileNameAsANumber = "3" 'Works to address First item sheet in line 100
58  'Dim FileNameAsANumber As Long: Let FileNameAsANumber = "3" 'Works to address Third item sheet in line 100
59  'Dim FileNameAsANumber As Long: Let FileNameAsANumber = 3 'Works to address Third item sheet in line 100
60  
70  Let ws1.Name = FileNameAsANumber

80  Let wb.Worksheets("" & FileNameAsANumber & "").Range("A1").Value2 _
= "Hi Norie from First Sheet that was renamed to " & """3""" & "!"

90  Let wb.Worksheets("" & FileNameAsANumber).Range("A1").Value2 _
= "Hi Again, Norie from First Sheet that was renamed to " & """3""" & "!"

100 Let wb.Worksheets(FileNameAsANumber).Range("A1").Value2 _
= "Hi Norie, this time from Third Item sheet ( Third Tab from the Left ) !"

End Sub

. I know that would very rarely happen as usually FileNameAsANumber would not be dimensioned as long, but it did just come out that way in a code and caught me out once


.. so I just got in the habit of always doing it
.
. Thanks for the reply
Alan

Why do you want an array of column indices?

Originally Posted by romperstomper

Why do you want an array of column indices?

Hi Rory,
. Bit of a long story: ( hey that rhymes nice...)
http://www.excelforum.com/excel-new-...ba-arrays.html

....".......quick “One Liner” at producing an Output Array derived from an Input Array using a code line of this form

arrOut() = Application.Index(arrIn(), rws(), clms())


where the Arrays rws() and clms() contain a list of "row" and "column" Indices of the rows and columns in the Input Array required in the Output Array. ...."........

Alan

If the problem with the code in post #21 was caused by having numeric sheet names you can use CStr to convert to a string.

PS To get a column letter from an address use Split, for example.

MsgBox Split(Range("AB1").Address, "$")(1)

Hi Norie,

Originally Posted by Norie

.....
PS To get a column letter from an address use Split, for example.

MsgBox Split(Range("AB1").Address, "$")(1)

. Thanks I didn't think of that one, ( I guess there are a few others, there usually are in VBA. – But so many and so crap documented that great there are you experienced guys to help us! )

……………………………..

Originally Posted by Norie

......If the problem with the code in post #21 was caused by having numeric sheet names you can use CStr to convert to a string......

.. Yeh, thanks another way to get over it. Just like having it Dimensioned as string as in some of the commented out lines in the code. Again probably a few other ways as well.

Thanks
Alan

Hi Ford,

I've been using Resize a lot lately:

ws2.Range("B2:D4").Value = 50

becomes:

ws2.Range("B2").Resize(3,3).Value = 50

Or:

ws2.Cells(2,2)).Resize(3,3).Value = 50