VBA- Error

Hi:  I get "Subscript out of Range Error when I run this 
code - any help is appreciated.  Thanks in advance. 

Dim CapSheetName(2) As String
        CapSheetName(0) = "Capitalized Expenditures_Hrs"
        CapSheetName(1) = "Capitalized Expenditures_%"
            
        For Each CapName In CapSheetName
            If sheets(CapName).Visible = True Then
               sheets(CapName).Activate
               ActiveSheet.Visible = False
            End If
        Next
0
10/20/2003 6:42:36 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
652 Views

Similar Articles

[PageSpeed] 22

Dipsy,

The loop is going around three times, for array items 0, 1, and 2.  On 2, it
fails trying to find a sheet name of "".  Change your array to

Dim CapSheetName(1) As String
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"dipsy" <dipsydoodleray@yahoo.com> wrote in message
news:011701c39739$ee55db40$a501280a@phx.gbl...
> Hi:  I get "Subscript out of Range Error when I run this
> code - any help is appreciated.  Thanks in advance.
>
> Dim CapSheetName(2) As String
>         CapSheetName(0) = "Capitalized Expenditures_Hrs"
>         CapSheetName(1) = "Capitalized Expenditures_%"
>
>         For Each CapName In CapSheetName
>             If sheets(CapName).Visible = True Then
>                sheets(CapName).Activate
>                ActiveSheet.Visible = False
>             End If
>         Next


0
nowhere8060 (363)
10/20/2003 9:02:45 PM
Dipsy,

I meant to add that you can use F8 to step through a loop to see what it's
doing when working on a problem like this.  Or put a breakpoint in it (F9),
then step it with F5 for one stop per loop.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <nowhere@verizon.net> wrote in message
news:OlJ1E20lDHA.988@TK2MSFTNGP10.phx.gbl...
> Dipsy,
>
> The loop is going around three times, for array items 0, 1, and 2.  On 2,
it
> fails trying to find a sheet name of "".  Change your array to
>
> Dim CapSheetName(1) As String
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "dipsy" <dipsydoodleray@yahoo.com> wrote in message
> news:011701c39739$ee55db40$a501280a@phx.gbl...
> > Hi:  I get "Subscript out of Range Error when I run this
> > code - any help is appreciated.  Thanks in advance.
> >
> > Dim CapSheetName(2) As String
> >         CapSheetName(0) = "Capitalized Expenditures_Hrs"
> >         CapSheetName(1) = "Capitalized Expenditures_%"
> >
> >         For Each CapName In CapSheetName
> >             If sheets(CapName).Visible = True Then
> >                sheets(CapName).Activate
> >                ActiveSheet.Visible = False
> >             End If
> >         Next
>
>


0
nowhere8060 (363)
10/20/2003 9:10:29 PM
Thanks.  
>-----Original Message-----
>Dipsy,
>
>I meant to add that you can use F8 to step through a loop 
to see what it's
>doing when working on a problem like this.  Or put a 
breakpoint in it (F9),
>then step it with F5 for one stop per loop.
>
>-- 
>Earl Kiosterud
>mvpearl omitthisword at verizon period net
>-------------------------------------------
>
>"Earl Kiosterud" <nowhere@verizon.net> wrote in message
>news:OlJ1E20lDHA.988@TK2MSFTNGP10.phx.gbl...
>> Dipsy,
>>
>> The loop is going around three times, for array items 
0, 1, and 2.  On 2,
>it
>> fails trying to find a sheet name of "".  Change your 
array to
>>
>> Dim CapSheetName(1) As String
>> -- 
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "dipsy" <dipsydoodleray@yahoo.com> wrote in message
>> news:011701c39739$ee55db40$a501280a@phx.gbl...
>> > Hi:  I get "Subscript out of Range Error when I run 
this
>> > code - any help is appreciated.  Thanks in advance.
>> >
>> > Dim CapSheetName(2) As String
>> >         CapSheetName(0) = "Capitalized 
Expenditures_Hrs"
>> >         CapSheetName(1) = "Capitalized Expenditures_%"
>> >
>> >         For Each CapName In CapSheetName
>> >             If sheets(CapName).Visible = True Then
>> >                sheets(CapName).Activate
>> >                ActiveSheet.Visible = False
>> >             End If
>> >         Next
>>
>>
>
>
>.
>
0
10/21/2003 7:31:02 PM
Reply:

Similar Artilces:

My syntax error
Hello there on this code I do receive a syntax error but I cannot figure out what. Is there something very obvious I am missing ? If you can, any help will be deeply appreciated ! Best regards to everybody Daniel Sub test() Dim F As String Spot = Sheets("Implied Dividends").Range("R3").Value Pricing_Date = Sheets("Implied Dividends").Range("D3").Value F = "=BS_Basic(TRUE,Pricing_Date,Sheets("FTSE").Range("F11"),Spot,Sheets("FTSE").Range("G11").Value,Sheets("FTSE").Range("IT11"),Sheets...

Macro Error...I Give Up
Happy New Year everyone! I am trying to run Data/Advanced Filter & copy filter result to another worksheet. When I manually do it, it works fine, however when I record a macro and run it, I get "Run time error '1004' Extract name has a missing or illegal field name. Any ideas? Here's the macro: Sub CopyFilterData2() ' ' CopyFilterData2 Macro ' Macro recorded 1/1/2005 ' ' Range("V7").Select Sheets("Entry").Range("A8:N31").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Entry")....

Hwt stop the error indicator?
How do i switch off the little green triangle that indicates an error in a cell. thanks Ok now sorted "ss" <yamxs750@blueyonder.co.uk> wrote in message news:duOEi.171725$p7.169759@fe2.news.blueyonder.co.uk... > How do i switch off the little green triangle that indicates an error in a > cell. > > thanks > > On Sun, 09 Sep 2007 09:23:30 GMT, "ss" <yamxs750@blueyonder.co.uk> wrote: >"ss" <yamxs750@blueyonder.co.uk> wrote in message >news:duOEi.171725$p7.169759@fe2.news.blueyonder.co.uk... >> How do i switch o...

Error: 800410A when trying to receive in Outlook 2002
I am using Windows XP; Outlook 2002 (XP) with Service Pack 3. I can send emails (using Adelphia cable for my ISP), but can't receive them. I get the error that the receiving POP server was found but the operation timed out waiting for a response from the receiving POP server (error 800410A). I have turned off my firewall and virus scan (McAfee). I have uninstalled and reinstalled Outlook. I have deleted and re-set my Account settings. I have no large emails in my web-based email account. I can't even receive the Outlook test message. Adelphia can't figure it out (they told...

CRM 4.0: Add "New Contact" from Contacts sub-screen in "Account" entity issues an error
There's a small advantage when you create a Contact within the Account entity: It carries over the Account Address, main phone etc.. to the newly created Contact so you won't have to re-type it all in. You can fill out the First & Last names only and you should be all set. There's a big problem though, It crashes when you hit "Save": -> "An error has occurred. ... Please contact your Organization's CRM Administrator" Had anybody ever tried this feature? and got to work? Thanks in advance! Regards, -Y. Do you have any customisations in pl...

PFbackup error
After installing pfbackup.exe, rebooting, initalizing Outlook 2K2 then running the new backup and exiting Outlook you get this message. Cannot copy Outlook: The process cannot access the file because another processs has locked a portion of the file. Do you have a PDA or Fax program installed, or any other program that accesses Outlook, such as a mail monitoring program? If yes, shut it down prior to exiting Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted...

GP 10.0 home page Runtime Error
Hello: This client was getting the "The specified module could not be found error", upon logging into GP 10.0 for the first time after a new install. I remedied this home page issue, by registering the msxml2.dll file in C:\WINNT\System32. Finally, this got the home page to at least appear. But, now we get this Runtime Error that says "Class not registered". We have installed the XML Parser and the Office 2003 Web Components and have registered the msxml3.dll file. No success. Any ideas? Security is ridiculously tight a this client. Would it do any good to mes...

"This application has requested..." error
Hi, I have a Deskband project that builds a DLL. When I register the DLL, I get the usual success dialog, but i also get "Visual C++ RunTime error" that says "This application has requested the Runtime to terminate in an unusual way. Please request product support for this application." I noticed that DLL has unloaded at this point. Also, my DLLRegisterServer is wizard generated and didn't add any code to it. Any ideas? Thanks Venkatesh ...

Integration Manager error message #3
Hi, I have created an integration with destination to be 'General Journal'. The problem I am having is that when I run that integration, it gives me an error message which says... "The Destination could not be initialized ... Error in loading DLL'. Has anyone experienced this error or any suggestions on how to fix this? Thank you -Ali ...

Public Folder Content Tab Error
In 2003 Exchange Admin, when you select the content tab on a public folder, it brings up an SSL certificate error (SSL is installed on the server for OWA). Is it trying to use IE to view it? Thanks. You only need to enable SSL on the Exchange virtual directory, sounds like you have it enabled on the Public directory as well, remove SSL from the Public virtual directory and see if that fixes it. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Randy Faulkner" <anonymous@discussions.microsoft.com> wrote in message news:1877901c44b...

A/R Posting Date Error
When voiding a cash receipt I used a future posting date in error. While this is only a timing issue, it make my reconciliations off each month until it catches up. Is there an easy way to correct the posting date. The only way I could think of is to repost the receipt to the wrong date and revoid with the correct date. This would get messy with multiple receipts however? -- DJC ...

X.400 Service error code 290
HI there, I require some assistance. We are running about a 20 user network and using Win NT SBS running Exchange 5.5 Thre is also a Linux Firewall setup which forwards incoming mail to the NT server. When sending mail the NT server forwards it to the Linux Box and then it gets sent. Everyone can send and receive both External and Internal email except one user who can do everything except send external email. The mail immediately bounces back with a error message from the administrator. When checking event viewer in the application log the following error has occured: A non-delive...

error ox800CCC7D
Why am I getting this message. The server does not support a SSL connection. Subject 'test' Server Error: 250 Server Response: 250 OK Server: 'smtp.comcast.net' Windows Live Mail Error ID: 0x800CCC7D Protocol: SMTP Port: 25 Secure(SSL): Yes Just what it says. You need to disable the unsupported SSL option: Go to Tools, Accounts, select that account, Properties, Advanced, then uncheck the SSL option.=20 --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "Walter Goldschmidt" <wgol...

Event Log Error 3018 MSExchangeTransport
I get heaps of these in my exchange 2003 server application event log Event ID: 301 Source: MSExchangeTranspor Category: ND A non-delivery report with a status code of 5.4.0 was generated for recipient rfc822;alison@mydomain.net (Message-ID <73h179e4pz4d$ax2a82d8$wz1760j6@JPMO70>). Causes: This message indicates a DNS problem or an IP address configuration problem Solution: Check the DNS using nslookup or dnsq. Verify the IP address is in IPv4 literal format They seem to be just NDRs for SPAM addresses so I don't even want to see them in the log Normal email is all working per...

Outlook (Sales CRM), Error when click on 'account' etc
If I try to select an items from any of the CRM folders in Outlook, I get the following error : Error An error has occurred. For more information, contact your system administrator. This is on Outlook 2003 (on Windows XP SP2). The CRM server is running on Windows 2003. NB: The port for CRM website is 8099 Sharepoint services is also running on the Windows Server. If I access the CRM via IE all is well (on the same Windows XP client) I've reveiewed all the updates for CRM 1.2 (KB), and installed any relevant patches. dont know if this will work but in the registry of the client machin...

VBA coding
Hi everyone I am very new to vba coding. Though time to time I record macros. Some I want to edit my macro but I don't the vba codes. It there a place where all the codes/coomanda are stored. thanks Nadeem Hi the easiest way to access your macros to edit them is to choose tools / macro / macros, click on the macro you want to edit and press the EDIT button - you'll be taken directly to the appropriate code. (to get back to the workbook you can either close the code window, using the top X or use ALT & F11 to switch back and forth between the VBE Window & the workbo...

capital gains error
When Money 2004 Premium calculates capital gains, it is incorrect when a sale occurs that includes more than 1 tax lot. It does not subtract the transaction cost from the sale. This gives a larger gain than is correct. One pays capital gains on the transaction cost. It works correctly when a sale occurs that has only 1 tax lot. This could cost users a significant amount of money. Has anyone else noted this serious error? In microsoft.public.money, Tom wrote: >When Money 2004 Premium calculates capital gains, it is >incorrect when a sale occurs that includes more than 1 >tax...

XML parsing error with schema
I have a simple XML file: <?xml version='1.0'?> <Employee xmlns='urn:MyEmployee-schema'> <Name> <First> Edward </First> <Last> Diener </Last> </Name> </Employee> and a schema to use to validate it: <?xml version='1.0'?> <xs:schema xmlns:xs='http://www.w3.org/2001/XMLSchema' targetNamespace='urn:MyEmployee-schema'> <xs:element name='Employee'> <xs:complexType> <xs:sequence> <xs:element name='Name'> <xs:complexType> <xs:sequence> <xs...

A lot of Error Messages
Hello, we use WLM to connect to our local IMAP Server (AVM KEN! 4) under Windows 7 64-Bit and Windows 7 32-Bit and we get lot's of Error Messages, about 5 per Minute. It just says "Unknown Error occured". Konfiguration: Konto: AnyIMAPAccount Server: 192.168.115.40 Benutzername: Accountname Protokoll: IMAP Port: 143 Secure (SSL): 0 Code: 80070057 Can anyone tell me what goes wrong here? Regards, Martin ...

Excell error BRSTSWND.exe
DWhat is this? I get it when i open an Excell spreadsheet I don't know what it is, but Chip Pearson has some techniques for diagnosing startup problems at: http://www.cpearson.com/excel/StartupErrors.htm David in Virginia wrote: > > DWhat is this? I get it when i open an Excell spreadsheet -- Dave Peterson ec35720@msn.com ...

[object Error] when trying to print
From one machine (mine, running Win XP SP2), I get an [object Error] when trying to print a report. I have never been prompted to install the ActiveX Crystal Viewer as has happened on other machines, even though I have reduced all of the security settings. Any thoughts on how this can be resolved? Thanks Leigh Try clicking on this link, to fix your Crystal printing problem:- http://crm/viewer/activexviewer/en/npviewer.exe Wait a few seconds, and you should get a prompt to install an ActiveX control. Select "Yes" or "OK" in response "Leigh Collett"...

XML Import errors not showing up
Hi, I'm using the application.XMLImport object to load a bunch of tables. One of the tables has funky characters that XML won't import, but it dosen't return errors. (I only found then when I tried to import manually). Does anybody know how to trap these errors? -- Posted via a free Usenet account from http://www.teranews.com ...

Why am I getting errors and problems with new e-mail client
"Message number 4 could not be retrieved. Account: 'pop-server.cfl.rr.com (1)', Server: 'pop-server.cfl.rr.com', Protocol: POP3, Server Response: '', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800420CD" NOTE: I get one of above errors for each mssage for number 4 through 11 and the one below is the last in the list. "Account: 'pop-server.cfl.rr.com (1)', Server: 'pop-server.cfl.rr.com', Protocol: POP3, Server Response: '', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Numb...

CRM3 Error when going off line 05-18-06
Whenever I try to take the laptop offline and click the 'go offline' button I am getting the error: "Failed move data for entity 'E-mail' during action saInsert, countRows=161. Contact your system administrator for assistance and try going offline again" Have tried going again with no luck any idea on what I should be doing to fix the error? Thanks I had the same error with one of my deployment. There's a KB on the MS partner site on that.Article ID : 916165 " Error message when you try to go offline in the Microsoft Dynamics CRM laptop client for Outlook...

VBA defining variables
Hi I have the code below. I set JJ=33, and I need Macro5 and Macro6 to use the variable JJ. But when it goes into those macros JJ is empty. Do you know how to fix this? Sub TestMacro2() Dim JJ As Double JJ = 33 Call Macro5 Call Macro6 End Sub Thanks for your help Because the variable is declared inside the sub TestMacro2, its 'scope' is limited to that sub. to make it more widely available you can either declare it outside the sub (making it 'global', which is easy but not appreciated by the programming gurus) or make it an argument to Macro5 and Macro6 (ex: decl...