Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Questions.
Answer hasn't changed:
Dim varr as Variant varr = Array("SheetA","Houses","Dogs") for i = lbound(varr) to ubound(varr) set sh = Worksheets(varr(i)) msgbox sh.Range("A1").Address(external:=true) Next Possibly you have added a reference to your project which doesn't exist. "suddenly crashing" is not self defining. People use that terminology to describe all sorts of situations. What is the specific error message that tells you excel has crashed? why would you expect to find an error message in a cell? -- Regards, Tom Ogilvy "The Wonder Thing" wrote in message ... Hello All! Allright, first of all I have a problem where a macro I've been using for ages suddenly is crashing. I didn't change the macro, so I've no idea what changed. There's no error messages in any of the cells that I can see. What could cause a problem to come up on this line? If wks.Name < "Crane Base Models" And wks.Name < "Labour" And wks.Name < "Misc Inventory" And wks.Name < "Materials" And wks.Name < "Materials" And wks.Name < "Components" Then It actually has a few more names in there, so if that's a problem, just let me know. Secondly, I'd like to know if there's a way to do things like that more efficently. I want to be able to loop through a bunch of names without doing that or this: Do While mySheetIndex <=3 If mySheetIndex = 1 Then mySheet = "Components" If mySheetIndex = 2 Then mySheet = "Labor" If mySheetIndex = 3 Then mySheet = "etc..." Worksheets(mySheet).Range("A1").Value = "There's a lot more stuff I want to do to a few specific worksheets than just change a value". Loop I was wondering if it's possible to use some kind of array to store a collection of certian worksheets, and then I could simplify things to something like: For each wkst in Worksheets If wkst.Name Like [An element in mySheetCollection] Then mydumb = MsgBox("Do this stuff here") End If Next It just gets so long and messy when I'm going through 30 different worksheets using either of those two loops. It'd be nice if I just wanted to effect them all, but I don't. Oh well. Any ideas? Thanks in advance! :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Questions.
wks is the only object in that statement. Perhaps you haven't set it to
reference a sheet. If it has worked in the past, it is unclear why it would now not work. -- Regards, Tom Ogilvy "The Wonder Thing" wrote in message ... Sorry Tom, didn't mean to post that question twice. I didn't think I posted that first one. Thanks for your quick answer. I'm pretty new at this. Trying to remember what little VBA I learned in highschool. It's giving me error 40036, "Application-defined or object defined error", I think is the exact message. I looked it up and it said that's the error it gives when it doesn't know what exactly caused the error in the first place. By error message in a cell I meant a #REF! or #VALUE! turning up. One of those errors coming up has caused my macros to crash before, so I checked for them again this time, but didn't see any. "Tom Ogilvy" wrote: Answer hasn't changed: Dim varr as Variant varr = Array("SheetA","Houses","Dogs") for i = lbound(varr) to ubound(varr) set sh = Worksheets(varr(i)) msgbox sh.Range("A1").Address(external:=true) Next Possibly you have added a reference to your project which doesn't exist. "suddenly crashing" is not self defining. People use that terminology to describe all sorts of situations. What is the specific error message that tells you excel has crashed? why would you expect to find an error message in a cell? -- Regards, Tom Ogilvy "The Wonder Thing" wrote in message ... Hello All! Allright, first of all I have a problem where a macro I've been using for ages suddenly is crashing. I didn't change the macro, so I've no idea what changed. There's no error messages in any of the cells that I can see. What could cause a problem to come up on this line? If wks.Name < "Crane Base Models" And wks.Name < "Labour" And wks.Name < "Misc Inventory" And wks.Name < "Materials" And wks.Name < "Materials" And wks.Name < "Components" Then It actually has a few more names in there, so if that's a problem, just let me know. Secondly, I'd like to know if there's a way to do things like that more efficently. I want to be able to loop through a bunch of names without doing that or this: Do While mySheetIndex <=3 If mySheetIndex = 1 Then mySheet = "Components" If mySheetIndex = 2 Then mySheet = "Labor" If mySheetIndex = 3 Then mySheet = "etc..." Worksheets(mySheet).Range("A1").Value = "There's a lot more stuff I want to do to a few specific worksheets than just change a value". Loop I was wondering if it's possible to use some kind of array to store a collection of certian worksheets, and then I could simplify things to something like: For each wkst in Worksheets If wkst.Name Like [An element in mySheetCollection] Then mydumb = MsgBox("Do this stuff here") End If Next It just gets so long and messy when I'm going through 30 different worksheets using either of those two loops. It'd be nice if I just wanted to effect them all, but I don't. Oh well. Any ideas? Thanks in advance! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
if questions | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
3 Questions | Excel Discussion (Misc queries) | |||
3 Questions | Excel Discussion (Misc queries) |