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...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

These all worked as expected.

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

Please Login or Register  to view this content.

I get a syntax error

What am I doing wrong?

Hi there,

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

and this does not...

Please Login or Register  to view this content.

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

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

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 ...

Please Login or Register  to view this content.

......

....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

Please Login or Register  to view this content.

.. 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 .

Please Login or Register  to view this content.

.. 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

Please Login or Register  to view this content.

. 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/

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

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

@ 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

Please Login or Register  to view this content.

.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 )

Please Login or Register  to view this content.

. 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:

Please Login or Register  to view this content.

. 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

'

Please Login or Register  to view this content.

FYI, there is no need for this:

Please Login or Register  to view this content.

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 )

Please Login or Register  to view this content.

. 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.

Please Login or Register  to view this content.

Why do you want an array of column indices?

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

Please Login or Register  to view this content.

. 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

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.

Please Login or Register  to view this content.

Hi Norie,

Originally Posted by Norie

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

Please Login or Register  to view this content.

. 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:

Please Login or Register  to view this content.

becomes:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.