Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determined to eliminate .select
When Bob and others say "Rarely if ever" should I use
select, then I am determined to eliminate it somehow. I have tried EVERWAY I know in this situation and I just can't get it. Ok, the situation is this: I'm in another worksheet, and I need to do this code: Sub Upload_MJE_to_As400() Range("Start").Select Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select How do I get there and eliminate this select line in this case when I'm somewhere else? Bruce |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determined to eliminate .select
Bruce,
Try something like Dim Rng As Range Set Rng = Range("Start")(1,1) Range(Rng.End(xlDown),Rng.End(xlToRight)).Select -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bruce Roberson" wrote in message ... When Bob and others say "Rarely if ever" should I use select, then I am determined to eliminate it somehow. I have tried EVERWAY I know in this situation and I just can't get it. Ok, the situation is this: I'm in another worksheet, and I need to do this code: Sub Upload_MJE_to_As400() Range("Start").Select Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select How do I get there and eliminate this select line in this case when I'm somewhere else? Bruce |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determined to eliminate .select
Hey Chip:
That worked, now help me understand the (1,1) part? Is that the same as cells(1,1) except that you don't use Cells since you're working with an object? Probably a dumb question, but I'm still not comfortable with working with objects and their syntax yet. And I had misstated when I put Range(ActiveCell.end(Xldown), activecell.end(xltoright)).select I changed that to ".copy" which is what I meant and that worked fine that way. So, selects are gone from that sub now. I only have 50,000 more to do away with that I have used while I've been learning VBA. Now, help me take this object to a different example, ok? Sub CurrValues() Sheets("MJE").Select Range("Start").offset(2,.).Select Selection.CurrentRegion.Clear I tried the following replacement for the selects, but I got a run time error 1004, application-defined or object defined error. The reason I keep selecting the sheet first and then the range is because it doesn't always work unless I do it that way. I think it has something to do with what sheet is active at the time I run this sub. But it drives me crazy when I see those error messages like that. With Select: Sub CurrValues() Sheets("MJE").Select Range("Start").Offset(2, 0).Select Selection.CurrentRegion.Clear Without Select: Sub CurrValues() Dim Rng as Range Set Rng = Sheets("MJE").Range("MJE")(3,1) Range(rng).Selection.CurrentRegion.Clear -----Original Message----- Bruce, Try something like Dim Rng As Range Set Rng = Range("Start")(1,1) Range(Rng.End(xlDown),Rng.End(xlToRight)).Selec t -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
Alan, I tried testing your solution below with .select
instead of .clear, just to make sure it worked if the worksheet mje is not the active sheet, and at first, that bombed with the error message 1004. Then, just for kicks, I changed the .select back to .clear just to make sure it didn't work that way. That evil .select really does cause those run time 1004 errors, because lo and behold, it worked with .clear when I was in a sheet other than mje and then executed the sub. Sometimes I just need a way to test my subs with .select first before I do something like clear something I didn't intend to clear. Is there a suggestion on how to test something you're not sure of in this case, before you run the risk of wiping something out you didn't intend to wipe out? I guess I could just save the workbook before trying something I'm not sure of. But then I'd be saving the workbook pretty darned often <LOL Thanks, Bruce With that in mind, try Sub CurrValues() Sheets("MJE").Range("Start")(3,1).CurrentRegion.C lear Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
Bruce Roberson wrote:
<I guess I could just save the workbook before trying something I'm not sure of. But then I'd be saving the workbook pretty darned often <LOL . . . .[snip] As a matter of fact, I save my test workbooks pretty darned often! It's just a couple of clicks. Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determined to eliminate .select
If the variable is only going to be referred to once, I would usually
recommend against it. You have to code in the range once in order to assign it to the variable--might as well let that once be the time it's going to be used. Alan Beban Bruce Roberson wrote: Yes, the second mje was a typo, and it was supposed to be the range start. So are you saying in this case, that I should not be declaring the rng as range and trying to set rng = to the range area? "Set Rng = "statement? Bruce -----Original Message----- Bruce Roberson wrote: Without Select: Sub CurrValues() Dim Rng as Range Set Rng = Sheets("MJE").Range("MJE")(3,1) Range(rng).Selection.CurrentRegion.Clear I assume that the second "MJE" is a typo and should be "Start". With that in mind, try Sub CurrValues() Sheets("MJE").Range("Start")(3,1).CurrentRegion. Clear Alan Beban |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
Bruce...
I see so many of your questions here recently They suggest to me that you've recently started with VBA Fine.. we all had to start someday. But would you go swim in a lake, if you didn't know how to swim? Then.. on the point of drowning.. You ask the lifeguard to please explain what a backstroke is? We'll keep helping you. That's not the issue here. Finally you'll learn how to swim. But YOU will get frustrated by this trial and error method, cause you lack an understanding of some BASICS. Step back from what your currently trying to do .... Close the workbook. Forget it for 1 day. Buy a book on VBA (preferably for Excel) Read the first few chapters... Really you'll spend maybe 1/2 day including your trip to the booksto) Then do a few exercises from the book. and take time to understand what your doing. That'll take another 1/2 day. Then go back to what you're working on now. I promise you that day reading and learning will be a day well spent keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Bruce Roberson" wrote: Alan, I tried testing your solution below with .select instead of .clear, just to make sure it worked if the worksheet mje is not the active sheet, and at first, that bombed with the error message 1004. Then, just for kicks, I changed the .select back to .clear just to make sure it didn't work that way. That evil .select really does cause those run time 1004 errors, because lo and behold, it worked with .clear when I was in a sheet other than mje and then executed the sub. Sometimes I just need a way to test my subs with .select first before I do something like clear something I didn't intend to clear. Is there a suggestion on how to test something you're not sure of in this case, before you run the risk of wiping something out you didn't intend to wipe out? I guess I could just save the workbook before trying something I'm not sure of. But then I'd be saving the workbook pretty darned often <LOL Thanks, Bruce With that in mind, try Sub CurrValues() Sheets("MJE").Range("Start")(3,1).CurrentRegion. Clear Alan Beban |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
I see so many of your questions here recently
They suggest to me that you've recently started with VBA Fine.. we all had to start someday. Not sure I should touch this post, however.... Yes, I have recently started working with VBA. I'm about 6 or 7 weeks into Excel and VBA transferring work files from Quattro Pro for Windows. And yes I've worked through a VBA book: Step By Step, Microsoft Excel 97 Visual Basic by Reed Jacobson. I didn't learn near as much from that book as what I've been able to learn by converting my own QPW spreadsheets over to Excel 2000. Some of my spreadsheets in QPW are very involved, and yea, some of the things I am trying to do now may be more of an intermediate to advanced stage of VBA. But again, I do believe I'm learning quickly from the information I've received from the responses to the posts I have been doing. Many many people here have taken the time and have been patient with me in their responses even when I didn't quite get it the first time around. But would you go swim in a lake, if you didn't know how to swim? Then.. on the point of drowning.. You ask the lifeguard to please explain what a backstroke is? We'll keep helping you. That's not the issue here. Finally you'll learn how to swim. But YOU will get frustrated by this trial and error method, cause you lack an understanding of some BASICS. Not quiet sure how to take this. Yea sure I get frustrated with myself because I expect of myself to become the leader in Excel that I have been for years in my company with Quattro Pro. But it certainly isn't all trial and error on here. I try new things and then if I can't figure them out of my own, I post for help, and I usually get a timely response. I am reusing the examples I pick up on here. Only in the past day or so have I intensified my efforts towards not just functional coding that gets the tasks done, but to strive to learn the most efficient means of coding... ie, not using .select as much in my coding. I'm still trying to grasp how to use objects as I'm sure you can see. I don't think I'm in any danger or drowning at all. I would of been perhaps if my only source had of been the books you think I should read before continuing on. Step back from what your currently trying to do .... Close the workbook. Forget it for 1 day. I'm almost done with my most challenging workbook in Excel, and so no way am I going to slack off now. I suppose if you get tired of reading my posts, then you have the option of not reading or responding to them... Like I said, maybe I should of just ignored this post, and maybe it wasn't intended to be caddy in any way... But at first glance, I kind of took it that way. Bruce |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
Bruce Roberson wrote:
. . . [snip] Not sure I should touch this post, however....[snip] That was probably the better choice. But for what it's worth, I wouldn't internalize that kind of negative criticism until I saw it coming from more than one source. Until then, you can't really tell whether the comments are more about you or more about the commentor. Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
What he was saying is that you are missing some of the basic concepts and
this is causing many of your problems. For instance, your post on Clear versus select. A basic fact is you can't select a range on a sheet unless it is the active sheet. It would be hard to recognize this is the problem. Another problem is that when code is placed in a sheet module, unqualified references refer to the sheet associated with the sheet module, not the active sheet. The same code in a general module would refer to the active sheet. Assume Sheet2 is active and you have code in the Sheet2 module Worksheets("Sheet1").Activate Range("A1").Select A1 on sheet2 is attempted to be selected - although the intent was probably to select A1 on Sheet1. Since Sheet2 is not the active sheet, an error would be raised. If the same code is put in a general module, A1 on Sheet1 *is* selected. These type anomolies are difficult to pick up through experimentation where a book might cover them. I believe that is the type of thought KeepitCool was trying to convey. Not criticizing, but saying that a good book that spells out some of the above type basic concepts might be a multiplier in terms of productivity and growth in ability. --- If you wanted to test a range before you take action on that range (rather than clear, see if the reference is what you want), then use address Sub CurrValues() msgbox Sheets("MJE").Range("Start")(3,1).CurrentRegion.Ad dress(0,0,xlA1,True) or Sub CurrValues() debug.print Sheets("MJE").Range("Start")(3,1).CurrentRegion.Ad dress(0,0,xlA1,True) -- Regards, Tom Ogilvy Bruce Roberson wrote in message ... I see so many of your questions here recently They suggest to me that you've recently started with VBA Fine.. we all had to start someday. Not sure I should touch this post, however.... Yes, I have recently started working with VBA. I'm about 6 or 7 weeks into Excel and VBA transferring work files from Quattro Pro for Windows. And yes I've worked through a VBA book: Step By Step, Microsoft Excel 97 Visual Basic by Reed Jacobson. I didn't learn near as much from that book as what I've been able to learn by converting my own QPW spreadsheets over to Excel 2000. Some of my spreadsheets in QPW are very involved, and yea, some of the things I am trying to do now may be more of an intermediate to advanced stage of VBA. But again, I do believe I'm learning quickly from the information I've received from the responses to the posts I have been doing. Many many people here have taken the time and have been patient with me in their responses even when I didn't quite get it the first time around. But would you go swim in a lake, if you didn't know how to swim? Then.. on the point of drowning.. You ask the lifeguard to please explain what a backstroke is? We'll keep helping you. That's not the issue here. Finally you'll learn how to swim. But YOU will get frustrated by this trial and error method, cause you lack an understanding of some BASICS. Not quiet sure how to take this. Yea sure I get frustrated with myself because I expect of myself to become the leader in Excel that I have been for years in my company with Quattro Pro. But it certainly isn't all trial and error on here. I try new things and then if I can't figure them out of my own, I post for help, and I usually get a timely response. I am reusing the examples I pick up on here. Only in the past day or so have I intensified my efforts towards not just functional coding that gets the tasks done, but to strive to learn the most efficient means of coding... ie, not using .select as much in my coding. I'm still trying to grasp how to use objects as I'm sure you can see. I don't think I'm in any danger or drowning at all. I would of been perhaps if my only source had of been the books you think I should read before continuing on. Step back from what your currently trying to do .... Close the workbook. Forget it for 1 day. I'm almost done with my most challenging workbook in Excel, and so no way am I going to slack off now. I suppose if you get tired of reading my posts, then you have the option of not reading or responding to them... Like I said, maybe I should of just ignored this post, and maybe it wasn't intended to be caddy in any way... But at first glance, I kind of took it that way. Bruce |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alan's response
Keep:
You're right on that I haven't made the step from scripting to programming. When you consider my background in 1-2-3 and QPW it is easy to see why that would be true, macros in 1-2-3 and QPW at least in early versions where I began are more scripting than programming. Then, if you couple that with the fact of my work experience being an accounting person rather than a programmer, you can see at least in part why I am the way I am. I continually have to remind myself about Excel in general, to quit thinking about the methods I used in QPW and just look to solve the problem in Excel, however it needs to be done. I am intent on identifying those weaknesses I have with regard to learning to be an expert in VBA. And, then I am intent on fixing those weaknesses, because I think that this stuff is cool when I get the results I want. I get excited when I figure out stuff in spreadsheets to the point I can't sleep at night thinking what I'm going to do next. Now that I know more about who you are and that you didn't mean anything "harsh" I can more readily accept what you have to say. Maybe Bruce skipped the theory and went straight to the examples. That's not so strange... he needs HIS workbook translated from QPW to Excel and he wants results.. NOW! Good observation again I think. Of course some people learn better by doing than by reading and studying. I am more of a visual learner than a book learner. Probably explains why I made so many C's in college , because I wanted results and not theory. <LOL Once I'm done with this last major workbook and then once I've gone back and tried to clean up some of the "functional" code of the first few weeks, then I'll try and look at others posts on here. I think Bob mentioned on here that the point of all this was to get each one to where they could start being a helper of others rather than just coming here for their own solutions (not his exact words, but the gist I got out of it). Later, Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM determined by drop-down list | Excel Worksheet Functions | |||
Getting a value from a cell that is determined by a formula | Excel Discussion (Misc queries) | |||
how are random numbers determined | Excel Worksheet Functions | |||
tabbing in a pre-determined direction | Excel Discussion (Misc queries) | |||
tabbing to pre-determined cells | Excel Discussion (Misc queries) |