Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
I'm a professional programmer but new to programming Excel. I recently found
an example on the internet that has the line 'Set rng = Range("Database")' in it. Everything works fine in the example. When I modify the code and try to use it on my spreadsheet I get an error on the 'Set rng = Range("Database")' line. The error is: Run-time error '1004': Method 'Range' of object '_Global' failed. Any ideas as to why it works on the example but errors on my spreadsheet? Thanks, Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
Hi Tom,
Set rng = Range("Database")' sets the range object rng to a worksheet range named Database. If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. The solution is either to name the requisite worksheet range in Excel or, alternatively, define the range directly, e,g, Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") --- Regards, Norman "Tom" wrote in message ... I'm a professional programmer but new to programming Excel. I recently found an example on the internet that has the line 'Set rng = Range("Database")' in it. Everything works fine in the example. When I modify the code and try to use it on my spreadsheet I get an error on the 'Set rng = Range("Database")' line. The error is: Run-time error '1004': Method 'Range' of object '_Global' failed. Any ideas as to why it works on the example but errors on my spreadsheet? Thanks, Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
Norman, thanks you shed some light on my problem. I still have a question or
two. You stated If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. that makes perfect sense but how do I "create a range named Database" and why don't I see anything named Database on the example? Your suggestion of Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") also makes perfect sense however, both the number of columns and rows will vary from time to time. How can I be sure I select all the data? Sorry to bother everyone with such a simple question but I have wasted an entire day on this and I am stumped! Thanks, Tom "Norman Jones" wrote: Hi Tom, Set rng = Range("Database")' sets the range object rng to a worksheet range named Database. If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. The solution is either to name the requisite worksheet range in Excel or, alternatively, define the range directly, e,g, Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") --- Regards, Norman "Tom" wrote in message ... I'm a professional programmer but new to programming Excel. I recently found an example on the internet that has the line 'Set rng = Range("Database")' in it. Everything works fine in the example. When I modify the code and try to use it on my spreadsheet I get an error on the 'Set rng = Range("Database")' line. The error is: Run-time error '1004': Method 'Range' of object '_Global' failed. Any ideas as to why it works on the example but errors on my spreadsheet? Thanks, Tom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
In article ,
Tom wrote: I still have a question or two. You stated If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. that makes perfect sense but how do I "create a range named Database" and why don't I see anything named Database on the example? One way is to select a range, then type "Database" in the Name box on the left side of the formula bar, then press Return. You can do it in code with Range("A1:A100").Name = "Database" IIRC, Database is also a built-in name for the area associated with a DataForm. As to why your example didn't define it, who knows? All you said was that you found it "on the internet". One can find al *kinds* of examples on the internet. Your suggestion of Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") also makes perfect sense however, both the number of columns and rows will vary from time to time. How can I be sure I select all the data? Sorry to bother everyone with such a simple question but I have wasted an entire day on this and I am stumped! How will your range vary? If you just want to get all the contiguous data around a particular cell (i.e., all data not separated from the cell by a blank vertical or horizontal range), use the .CurrentRegion property of the Range object Set rng = Range("A1").CurrentRegion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
Just another way.
Most of the time, I can pick out a column that always has entries (some index/key perhaps). And I can pick out a row that always has entries (headers???). Then I can do things like: dim myRng as range dim LastRow as long dim LastCol as long with worksheets("sheet1") lastrow = .cells(.rows.count,"A").end(xlup).row lastcol = .cells(1,.columns.count).end(xltoleft).column set myRng = .range("a1",.cells(lastrow,lastcol)) end with ==== Another option if you can trust to use xl's lastused cell: dim myRng as range with worksheets("sheet1") set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell)) end with But excel keeps pretty strict track of what you've ever used. If you put a value in x99 and then clear contents, you can see what I mean if you hit ctrl-End. You'll be taken to whatever excel thinks is the lastusedcell--not always what you would have thought. Sometimes just using .usedrange in code will reset that lastusedcell (sometimes not). dim myRng as range with worksheets("sheet1") set myrng = .usedrange 'try to reset usedrange set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell)) end with Tom wrote: Norman, thanks you shed some light on my problem. I still have a question or two. You stated If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. that makes perfect sense but how do I "create a range named Database" and why don't I see anything named Database on the example? Your suggestion of Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") also makes perfect sense however, both the number of columns and rows will vary from time to time. How can I be sure I select all the data? Sorry to bother everyone with such a simple question but I have wasted an entire day on this and I am stumped! Thanks, Tom "Norman Jones" wrote: Hi Tom, Set rng = Range("Database")' sets the range object rng to a worksheet range named Database. If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. The solution is either to name the requisite worksheet range in Excel or, alternatively, define the range directly, e,g, Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") --- Regards, Norman "Tom" wrote in message ... I'm a professional programmer but new to programming Excel. I recently found an example on the internet that has the line 'Set rng = Range("Database")' in it. Everything works fine in the example. When I modify the code and try to use it on my spreadsheet I get an error on the 'Set rng = Range("Database")' line. The error is: Run-time error '1004': Method 'Range' of object '_Global' failed. Any ideas as to why it works on the example but errors on my spreadsheet? Thanks, Tom -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does 'Set rng = Range("Database")' do?
Thanks to all, you answered my questions and I solved my problem!
Tom "Dave Peterson" wrote: Just another way. Most of the time, I can pick out a column that always has entries (some index/key perhaps). And I can pick out a row that always has entries (headers???). Then I can do things like: dim myRng as range dim LastRow as long dim LastCol as long with worksheets("sheet1") lastrow = .cells(.rows.count,"A").end(xlup).row lastcol = .cells(1,.columns.count).end(xltoleft).column set myRng = .range("a1",.cells(lastrow,lastcol)) end with ==== Another option if you can trust to use xl's lastused cell: dim myRng as range with worksheets("sheet1") set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell)) end with But excel keeps pretty strict track of what you've ever used. If you put a value in x99 and then clear contents, you can see what I mean if you hit ctrl-End. You'll be taken to whatever excel thinks is the lastusedcell--not always what you would have thought. Sometimes just using .usedrange in code will reset that lastusedcell (sometimes not). dim myRng as range with worksheets("sheet1") set myrng = .usedrange 'try to reset usedrange set myrng = .range("a1",.cells.specialcells(xlcelltypelastused cell)) end with Tom wrote: Norman, thanks you shed some light on my problem. I still have a question or two. You stated If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. that makes perfect sense but how do I "create a range named Database" and why don't I see anything named Database on the example? Your suggestion of Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") also makes perfect sense however, both the number of columns and rows will vary from time to time. How can I be sure I select all the data? Sorry to bother everyone with such a simple question but I have wasted an entire day on this and I am stumped! Thanks, Tom "Norman Jones" wrote: Hi Tom, Set rng = Range("Database")' sets the range object rng to a worksheet range named Database. If you do not have a range named Database on your sheet you will get an error message of the type that you have encountered. The solution is either to name the requisite worksheet range in Excel or, alternatively, define the range directly, e,g, Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:D100") --- Regards, Norman "Tom" wrote in message ... I'm a professional programmer but new to programming Excel. I recently found an example on the internet that has the line 'Set rng = Range("Database")' in it. Everything works fine in the example. When I modify the code and try to use it on my spreadsheet I get an error on the 'Set rng = Range("Database")' line. The error is: Run-time error '1004': Method 'Range' of object '_Global' failed. Any ideas as to why it works on the example but errors on my spreadsheet? Thanks, Tom -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Pivot Table Range ("Database") | Excel Discussion (Misc queries) | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming | |||
Change "relative" to "absolute" (database) | Excel Programming |