Code is not working

abeed

Board Regular
Joined
Nov 19, 2005
Messages
79
Hi:
The following code giving application error:

rs.Range(Cells(sr, 2), Cells(sr, 13)).Value = ds.Range(Cells(tr, cn), Cells(tr, cn + 11)).Value

--
rs is my rport shhet,ds is my data sheet in 2 different workbook. I am trying to copy from ds to rs.
at the first loop iteration:
sr =10,tr=10,cn=2

--Thanks in advance
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
A very good principle to remember is that nothing inside the XL object hierarchy exists *except* in reference to the application object. In some ways it's a shame that MS implemented default and implicit parent references. Cells (or Range) w/o is a *property.* It must be applied to some object. For a physical object imagine a statement like Door(Passenger, front)=blue. It's a meaningless statement unless you know what car the reference is to.

MS, in trying to be helpful, decided that an unqualified use of the Cells (or Range) property will apply to the active sheet (check help for the 'Cells property').

The result is that rs.range(cells(),cells()) is the equivalent of rs.range(activesheet.cells(),activesheet.cells()). And, since a range can have only one parent worksheet, the construct results in an unresolvable conflict *unless* rs is the activesheet.

Dots are not binding on anything. I don't know what that means. rs.range is simply a way of saying the range property of the rs object. A property (and a method for that matter) often needs an argument (just like a sub or a function).

Maybe, a physical object will help. Consider an object of type 'car.' By itself this isn't of much use. We have to use properties or methods to do something with the object. Suppose we know that there's a property called Doors. Doors() is a property of the Car class and it will return an object that is a collection of all the doors on the car. OK, so we have an object of type collection. Again, there's not much we can do with it unless we know that it has a property called Item. This Item property takes two arguments to identify a specific door. These indices can have the values: Passenger/driver-side and front/rear. So, MyCar.Doors.Item(Passenger,Front) refers to a specific door. Often, the programming language lets one skip the Item property of a collection and jsut refer to MyCar.Doors(x,y).

Two important lessons so far. A property needs an object to be meaningful. An object is useless unless one knows something about its properties and methods. This forms a very symbiotic relationship since one cannot exist without the other.

Often, in the interest of efficiency (fake efficiency, IMO), a programming language implements a default object to apply an unqualified property (or method) and a default property for an unspecified property. We see this heavily misused in something like anInteger=Cells(i,j). As an exercise, figure out how many assumptions about objects and properties were required to make that statement work.

Also, keep in mind that a property always returns something of some type. It can be a basic data type (integer, string, etc.) or it can be a object of some kind (and a collection is itself a type of object). Another thing worth noting is that a property named X can return something of type X. Nothing wrong with that. In fact, MS implemented that convention extensively in its various OMs. That is how we get the Range property of a worksheet object returning an object of type Range as in Set aRng=WS.Range(). In fact, the Range property of the Range object returns an object of type Range as in Set aRng=Range("A1:A10).range("A1")..

OK, a final but detailed example: How does one refer to the name of the first sheet of the first opened workbook? The fully qualified reference would be Application.workbooks.item(1).sheets.item(1).name. This returns an object of type string. To really understand what is going on you have to realize how compact OO programming can be and should invest time in deconstructing these statements.

[For another example see
Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm]

Back to our current example. Remember I wrote that *everything* in the XL OM flows from the application object?

So, start with the Application object and use its workbooks property. That returns an object of type workbooks collection. The generic type of this object is collection.

As mentioned above, collections have their own properties and methods. One of them is Item() which lets one refer to a specific item in the collection. So, the Item() property returns an object of whatever type is in the collection. This is an instance where the name of the property doesn't match the name of object type returned by it.

So, Application.workbooks.item(1) returns the first 'thing' that is in the collection. Since the workbooks collection contains only workbooks, we can be confident that the type of the returned object is 'workbook.'

The workbook object has a property named Sheets. It returns a collection of all the sheets in the workbook. Remember that sheets in a workbook can be four different types (Chart, Worksheet, XL4 macro, and DialogSheet). So, the Sheets collection -- unlike the Workbooks collection -- doesn't necessarily have homogenous objects in it.

We use the same Item property that every collection has to 'get' the first object. The generic type of this object is sheet and it may also belong to a more specific type of a collection (but we will leave that concept for another day).

Luckily for us we don't need to know what kind of a sheet we are dealing with since *every* type of sheet has a name property.

So far every property has returned an object of some kind. The last property we use (Name) returns a basic data type: string. Well, at least in VBA string is a basic data type. In other languages it is not necessarily so. But, we will leave that too for another day.

Note that the Item property is so 'fundamental' to a collection that MS doesn't require one to use it. That means Collection.Item(x) is the same as Collection(x).

Instead of accessing the name of the first sheet of the first workbook in a single statement, one could break it down into individual steps. It really helps to understand what is going on. I do this when investigating an object I am not familiar with. It really helps understand the hierarchy of the otherwise unknown object.
Code:
    Dim AllWBs As Workbooks, aWB As Workbook, _
        AllSheets As Object, aWS As Worksheet
    Set AllWBs = Application.Workbooks
    Set aWB = AllWBs.Item(1)
    Set AllSheets = aWB.Sheets
    Set aWS = AllSheets.Item(1)
    MsgBox aWS.Name

just_jon said:
jmiskey said:
Thanks jon. I learned something new today.

It looks like the key is that you have to preface EVERY range reference with the Worksheet variable; not just the ones that start RANGE, but also the ones that start CELLS. I guess the reference in front of RANGE doesn't carry over all the way through.

Not sure, this is behavior I'm not familiar with - the structure

Workshee('ref1").Range(Cells(rowref, colref), Cells(RowRef, ColRef)) = ...

should be sufficient as the period should bind the Range to the Worksheet, no?

I'm just not seeing the obvious...
 

Ronald Moore

Board Regular
Joined
Aug 22, 2005
Messages
101
abeed said:
Hi:
The following code giving application error:

rs.Range(Cells(sr, 2), Cells(sr, 13)).Value = ds.Range(Cells(tr, cn), Cells(tr, cn + 11)).Value

--
rs is my rport shhet,ds is my data sheet in 2 different workbook. I am trying to copy from ds to rs.
at the first loop iteration:
sr =10,tr=10,cn=2

--Thanks in advance
I suggest
Code:
rs.Cells(sr, 2).Resize(1, 12).Value = ds.Cells(tr, cn).Resize(1, 12).Value
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Sorry, was not "speaking" clearly [ nor thinking so :unsure: ] -

Dots are not binding on anything.

What I meant was that in rs.Range() rs is the predicate object for Range. I use the word "bind" because it helped me to first understand the difference between

Code:
With Object
        .Range ' and
        Range ' i really need coffee ...
End With

I'll slink back to my room now... thanks again, Tushar - appreciate the drop-by.

And, Ronald - why is it that I never remember Resize? OMG! I *really* am that dense!
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Think I'm gonna find a smart person ... brb ...

So I'm not the only one who IM's poor Tusharm? :)

Hey Tusharm! Since your here....

http://www.mrexcel.com/board2/viewtopic.php?p=1073244#1073244

Can't remember if I PM'd him then, or just channeled his presence. :biggrin:

I *do* know that I have reached out to him, and others -- you included, Tom -- often in the past.

And I always appreciate the insight and advice.

As in the above, how much $$$ do you think the above tutorial would have cost me had I gone looking to buy this level of expert advice? My guess is: much more than I have in my pockets.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top