subscript out of range error

My program went down with on error in the code.  I fixed that error an
tried to re-run it. When I go to re-run the program I get a subscrip
out of range error when I try to open a worksheet that I know exists.
If I close the workbook and re-open it the subscript out of range erro
doesn't show up.  Below is the code.

With Worksheets("ListBox Data").Range("A2")
workbook_row_cnt = Range(.Offset(0, 0)
.End(xlDown)).Rows.Count       
For i = 0 To workbook_row_cnt - 1
If .Offset(i, 0) <> "" Then
sales_group_cnt = sales_group_cnt + 1  
End If
Next i
End With


I am new to this but is it recommended that a worksheet be deactivate
and then activated before each use? 

You help would be appreciated.

Thank

--
Message posted from http://www.ExcelForum.com

0
8/9/2004 1:10:49 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
519 Views

Similar Articles

[PageSpeed] 7

It could be as simple as you dont have the period   .     before
Range(.Offset(0, 0),

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"cparsons >" <<cparsons.1aq2zz@excelforum-nospam.com> wrote in message
news:cparsons.1aq2zz@excelforum-nospam.com...
> My program went down with on error in the code.  I fixed that error and
> tried to re-run it. When I go to re-run the program I get a subscript
> out of range error when I try to open a worksheet that I know exists.
> If I close the workbook and re-open it the subscript out of range error
> doesn't show up.  Below is the code.
>
> With Worksheets("ListBox Data").Range("A2")
> workbook_row_cnt = Range(.Offset(0, 0),
> End(xlDown)).Rows.Count
> For i = 0 To workbook_row_cnt - 1
> If .Offset(i, 0) <> "" Then
> sales_group_cnt = sales_group_cnt + 1
> End If
> Next i
> End With
>
>
> I am new to this but is it recommended that a worksheet be deactivated
> and then activated before each use?
>
> You help would be appreciated.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
8/9/2004 2:44:31 PM
C,

I've not heard of deactivating and activating a worksheet.  Just rebooting
every day or two! :)

You don't say when it starts failing again after reopening, nor which line
it's failed on.  When it does, try looking at i (use a watch, or put it on
the status bar with application.statusbar = i or something.

The .Offset(0, 0) doesn't seem to be needed, and the comma after it is
suspect.  Typo?  You can copy code from the VBE and paste it into an email
or post, and vice versa.  Just watch for line breaks.

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

"cparsons >" <<cparsons.1aq2zz@excelforum-nospam.com> wrote in message
news:cparsons.1aq2zz@excelforum-nospam.com...
> My program went down with on error in the code.  I fixed that error and
> tried to re-run it. When I go to re-run the program I get a subscript
> out of range error when I try to open a worksheet that I know exists.
> If I close the workbook and re-open it the subscript out of range error
> doesn't show up.  Below is the code.
>
> With Worksheets("ListBox Data").Range("A2")
> workbook_row_cnt = Range(.Offset(0, 0),
> End(xlDown)).Rows.Count
> For i = 0 To workbook_row_cnt - 1
> If .Offset(i, 0) <> "" Then
> sales_group_cnt = sales_group_cnt + 1
> End If
> Next i
> End With
>
>
> I am new to this but is it recommended that a worksheet be deactivated
> and then activated before each use?
>
> You help would be appreciated.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
8/9/2004 2:54:38 PM
Turns out I had somehow added a space to the actual name on the Tab.  
must have looked at it 10 times.  I didn't see it until I tried t
rename the tab.

Thanks for your help

--
Message posted from http://www.ExcelForum.com

0
8/11/2004 8:43:42 PM
Reply:

Similar Artilces:

Help! Odd Error Message in Outlook 2002????
Help! I've been trying to send some distro. e-mails through outlook. (Well under the e-mailnumber limit set by my e-mail provider). I have gotten at 50% or more sent back for the above message, the e-mail address it couldn't send to, and "(non-printable character)" behind it. I called my provider and they said it was a microsoft error. What in the world is wrong? It took me 2 hours to send 278 e-mails the other day!!! Below is the message minus the e-mail address. It repeats this in the e-mail for every address it did not send. And it's not just one provided tha...

outlook error #49
For no obvious reason my outlook freezes on start up and gives me several errors. -virtual memory is low -program needs the Windows Installer to run(or similar) I transfered my files to Express and this works, but I don't want to use Express-I want to use Outlook. I did a Window XP and a Outlook repair(in that order). I uninstalled outlook and reinstalled it. Still same problem. This is the only program that is giving me any trouble. I have all of Microsoft Office Suite installed on my machine. Any Ideas?!! Jim <jim@discussions.microsoft.com> wrote: > For no obvious...

Errors with per Week Chart
Hello, the sheet I'm working on records the number of seizures per da of a specific patient. I then have a sperate column which has the su of the seizures in a given week. Obviously, these sums appear ever seven cells. Adjacent to these cells is the date of the given week. So for example, the total amount of seizures in the week of 12/18/05 is 18 Unfortunately, to protect the patient's privacy, I cannot provide download to the actual document. Now, I'd like to graph the amount of seizures per week. When I try t do this, I either get a bunch of different series and no apparent wa...

Problems with Subscriptions after Migration
We are having an issue with our subscriptions after we migrated from a server running SQL Server 2005 32-bit under Windows Server 2003 R2 32-bit to a new server running SQL Server 2005 64-bit under Windows Server 2008 64-bit. Most of our subscriptions used aliases for the email address. I know this is no longer supported, so I'm trying to edit all our subscriptions. When I do this and click save, I get an error of the specified @name already exists. This also occurs when I create a new subscription and then try to edit it. Any suggestions? Thanks! Hi Jason, Why did...

SQL Error during data migration
Hi to all, we got a serious problem when loading data with the data migration framework. The MigrationTool throws an error after creating the account record in the crm database and then writing back the migration status. That means the according record in the cdf database is not receiving the new destination id so the tool uses the same data for creating the next account! This is an never ending loop. It is not our first load and the domain account we use is the same as admin in crm. We used it to load the data to the cdf, to cleanse the data in cdf (what means updates are possible with...

Error messages #16
I keep receiving 2 errors. User.PST file not found and when I enter my email server, Outlook tells me it can not be found. I have verified the server name. This is causing problems when I try to export my messages and address book from Outlook express. Please help. Whats the first err msg *exactly* when you first open whatever version of Outlook you have? "CC" <CC@discussions.microsoft.com> wrote in message news:2D287EFE-C600-4025-8AB7-CC381718071E@microsoft.com... >I keep receiving 2 errors. User.PST file not found and when I enter my >email > server, Out...

Error After Reinstall
I had to reformat the hard drive on my laptop and reinstall everything. When I open my main database, I get the following error: The database is in an unexpected state. MSAccess can't open it. The database has been converted from a prior version of Access by using DAO compact database method instead of the convert Database command on the tools menu. It instructs to import everything into a new database. I did not convert the database. After the message, I can open the database from the file menu. Can anyone tell me what happend here? Anne Now, I have created a new database and import...

Cannot assign a range to seriecollection values
I use the following code to assign a range in my macro. I checked with other webite and the syntax seems to be correct. Activesheet.chartobjects("Chart 1").activate set rng = activesheet.range(cells(1,1), cells(x, y)) Activechart.seriescollection(1).values = rng x and y are variables I set earlier in the code. The error message I get is "run time error 1004. Unable to set the values property of the series class Thanks for the help. What does the .values property of SeriesCollection(1) refer to prior to setting it to that range? If it contains all blanks or all errors, and...

MSExchangeAL 8331 Error and Hotfix Questions
Ok..I get the 8331 event every minute. I noticed that newly created email accounts are not showing up in Global Address list and after reading problems associated with the 8331 error,I figured that they were related to each other. Who has installed the hotix? Does the hotfix seem to fix the error showing up in application log and also fix any related email issues? What requirements are there with the hotfix...such as does it require users to be out of email or require a server reboot? Scott, I have had some of my customer install the hotfix, from their reports to me it has resolved the probl...

Swing Chart with dynamic Range
I would like to create a swing chart (stock chart) with a dynamic range that grows as more data is added. Any ideas? Thank You Joe Hi, For information on the dynamic ranges have a read of Jon Peltier's page http://peltiertech.com/Excel/Charts/Dynamics.html Depending on which stock chart you want you will need 3 to 5 named ranges. Cheers Andy Joseph Sayah wrote: > I would like to create a swing chart (stock chart) with a dynamic range that > grows as more data is added. > > Any ideas? > > Thank You > Joe -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

VBA
I'm replacing a character with another and then subscripting that new character. My code seems to work fine except when the object has multiple paragraphs. The last routine below (SubScriptMe) is called to subscript, but with multiple paragraphs it's counting the paragraph marker and screwing up the count to subscript the correct character. <argh!> Is there a better way to accomplish this? Or is there a way to work around multiple paragraphs. Thanks, Melina (code below) Sub SearchReplaceSubscript() Dim oSld As Slide Dim oShp As Shape Dim sSearchFor As String ...

Count cells in range
I need a function that simply counts the number of cells in a range. Not based on a condtion, simply the number of cells in a range. Obviously if I'm doing this once I can simply put in a constant. (It's easy to see that the range A1:A50 has 50 cells.) But I need a formula that will automatically adjust if the size of the range changes, so that if I insert a row after A17, the formula will return 51. As far as I can tell, COUNT, COUNTA, and COUNTIF don't do this. I suppose I could use COUNTIF(A1:A50,"<>999"), where 999 is a value that I know will not app...

Syntax Error WHY?
I have a subform on an Unbound form. I am running a Function from a command button on my subform. I am getting a Syntax Error in my Update statement. Can anyone tell me WHY? Function QClose() On Error GoTo QClose_Err Dim sql As String Dim strWhere As String Dim strMsg As String Dim frm As Form With CodeContextObject strWhere = "WHERE " & .Filter End With strMsg = "FILTERED records will be updated to Closed." If (MsgBox(strMsg, 273, "Warning") <> 1) Then 'Update filtered records on subf...

printing error
Hi, While i try to print a MS CRM 2.1 report from any user machine i get an activeX error that denies printing. even if it's an admin user but it works fine if i try printing from the server itself any idea on this problem Regards, Hanady Hi, I've been facing this error 2 weeks now. My customer is not able to print any report Your help is badly needed. Regards, Hanady "Hanady" <hanady.abousamra@trinec.com> wrote in message news:eU6QPc0GGHA.3828@TK2MSFTNGP10.phx.gbl... > Hi, > > While i try to print a MS CRM 2.1 report from any user machine i get a...

Date Range query design
I am working on an expenses database for a small family business. For each expense I have a start date and end date. I have the database then “normalize” the expense. So for instance, it takes a bill for a thirty day period and divides the total expense by thirty to give an Expense per day for the bill. I want to be able to query a range of dates (like a week or month period) and have the query determine how many of the days (if any) of an expense fall within that range and multiply that number of days by the “normalized” Expense per day value to give the contribution of that bill t...

Subscript
I am looking for a quicker way to superscript and subscript text rather than going to "format cells" and then clicking on subscript. In Word, there is a specific toolbar command for this, but i have not found the same in excel- when writing out long equations with superscript and subscripts, it would save time to be able to click a button. Is this possible in excel? You may like John Walkenbach's addin: http://j-walk.com/ss/excel/files/supersub.htm deirdre7 wrote: > > I am looking for a quicker way to superscript and subscript text rather than > going to "...

MS Money 2001 Runtime Error
Hi there I booted my computer like any other day and when I selected to run MS Money 2001, I got a "Microsoft Visual C++ Runtime Library" error screen. The box read: "Run Time Error!" [displays where the 'msmoney.exe' program is stored] and read below it: "This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. I can only locate the product ID by running the program in which the program only gives me the error screen and terminates. I can't locate an adequate...

Data Type mismatch error 3464 in Dlookup
Here is the code: Private Sub id_Part_AfterUpdate() strFilter = "pm_Part = " & Me!id_Part Me!id_Unit = DLookup("pm_Retail", "Parts", strFilter) End Sub Hi. I am getting error 3464 Data Type mismatch when I run this code. The field id_Unit is on the form and is of the type Currency. The field pm_Retail is in the Parts table and is of the type Currency. The field id_Part is on the form. The field pm_Retail is in the Parts table. This seems simple enough but I can't figure out what the type mismatch is. I would appreciate any sugges...

MS CRM 1.2 on MSDN Subscriptions
Do you have any idea when we should expect MS CRM 1.2 in MSDN? During MS IT forum in Denmark, I was told that it would be available until 6th of December, but nothing happened yet... Thanks. Doros As Microsoft are only now shipping 1.2 to partners etc I would not expect to see CRM on msdn for a month at least. The msdn shipping highlights screen does not yet show CRM 1.2 for the jan 2004 shipment but of course this could change over night. I'll post here if I find out when it will ship on MSDN "SiEBEN Innovative Solutions" <newsgroup@sieben.gr> wrote in message new...

Is there any way to give an dynamic name for a range?
I know we can define dynamic ranges. I am just curious about the names. Is there anyway to give it a dynamic name? For example, if I refer to another cell which contain the year when define some tables. Is there any way to name a range so that when the year is 2005 then the name would be "Report2005" and next year the name would be automatically changed to "Report2006"? I am trying to put formulas in insert - > Name - > Define - >Name in workbook. But looks like I am not allowed to do so. I hope there is a way other than VBA. Thanks a lot! Dear salut, Yo...

Technet subscription
Hello. I have some customers wishing to evaluate MS PoS for their shops and have been trying to set this up using the evaluation version in our Technet subscription. However, it appears we are only getting one activation out of our Product Key. Is this correct, or should we have the 10 activations as per the rest of the Technet evaluation software? Regards, Rob Cowan. Mycourse Limited. ------=_NextPart_0001_25D9AC07 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Rob, Thank you for posting your question about Product Keys. I am not sure of what should be i...

Dynamic range not working with Advanced Filter
I've got 8 ranges all setup as dynamic with the following formula: =OFFSET(sheet!$A$2,0,0,COUNTA(sheet!$A:$A)-1,1) 6 of the 8 ranges are having their ranges defined properly. I don't know why the other two are not. I've attached a pic of how they're laid out. When I go to the Names list and click on the formula for Column B it includes the two empty cells below the last number. Advanced filter fails, probably because of that. The formula is identical to the others that work... When I go to the Names list and test the range for Column G, the range is appropriately identif...

Define range for graphs/charts with validation list
Hi I would like to change the range for a graph with a dropdown list See attached picture to get the feeling. I don't want to have 12 chart for each month, but one graph and a validation dropdown list with whic I can dictate the graphs' data ranges. If I select January in the dropdown list, I must take the january rang for the graph, etc. I do not know if this is possible, but if it is possible that would b an awesome solution +------------------------------------------------------------------- |Filename: example.jpg |Download: http://www...

CRecordset getting ambiguous column error upon SQLGetData to SQL Server 2000
I have a CRecordset with the following SELECT statement used for opening the initial query: CString CEmailList::GetDefaultSQL() { return _T("SELECT el.CompID, el.Email, Subject, el.FormID, Field0, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Attachments, Attachment, FormDocument") _T(" FROM [dbo].[Email_List] el") _T(" LEFT OUTER JOIN [dbo].[Email_Forms] ef ON (ef.FormID=el.FormID)") _T(" LEFT OUTER JOIN [dbo].[Email_Attachments] ea ON (Attachments=1 AND ea.CompID=el.CompID AND ea.Email=el.Emai...

CRM email error 01-29-04
When I send email from Sales for Outlook or web interface it'd give me error: "An error has occurred. For more information, contact your system administrator." If I click "Go back" then send again it'd appear to send fine with no error. But, when I look in Activities I see 2 emails sent, one with CRM GUID and another without. Please help. Hi, Sorry to jump on your thread! Im having the same issue, im trying to send email from the web based version from one crm user to another as a test. All i get is the message an error has occurred. I click back and try ...