Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-11-2009, 09:04 PM
KHTAY KHTAY is offline
Novice
Subscript out of range error
 
Join Date: Jan 2009
Posts: 3
KHTAY is on a distinguished road
Default Subscript out of range error

Hi All,

I am new to this forum. Hope u guys can help me wif my problem.

I developed a small tool for my company using EXCEL 2003 - VBA. It works well on some machine but not on others. For few machine, they would get the above error. I have a master sheet on the excel where user click 'Refresh ALL' button, all data on all the sheets will be updated with current data and each sheet has a button to refresh its data too. The button on each sheet works very well. But for the 'Refresh All' button, user will encounter the error. Below is the part i suspected is giving the error:



Sheet2.Range("A3").CopyFromRecordset rs

Hope u guys out there can help me. Thks in advance.
Reply With Quote
  #2  
Old 01-12-2009, 02:04 AM
caholmes caholmes is offline Subscript out of range error Windows Vista Subscript out of range error Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default Subscript out of range error

This error normally is a result of refering to an object that does not exist or you cannot access. In your case you may find that the users log on credentials don't allow you to access the recordset (datasource). Try each machine to see if you can access the data manually first to rule out a network security issue.
Reply With Quote
  #3  
Old 01-12-2009, 02:56 AM
KHTAY KHTAY is offline
Novice
Subscript out of range error
 
Join Date: Jan 2009
Posts: 3
KHTAY is on a distinguished road
Default

Hi. Thks for ur reply

User can actually retrieve data from each individual 'refresh' button for each sheet. But when user tries to retrieve data for all sheets (18 sheets) from the master sheet, user will encounter the problem.....

Sheet1 (Master sheet) -> 'Refresh All' button = retrieve data for all sheets (error!)
Sheet2 to Sheet18 -> each have a 'Refresh' button = retrieve data for each individual sheet (No error)

Below is a sample code for 1 of the refresh button. This sub will be tied up to a button on a sheet :

Sub RefreshKPI101()
Dim rs As Object
Dim sProc As String
Set rs = CreateObject("ADODB.Recordset")

sProc = "EXEC BUD_AF_GM '" + sBranch + "','" + sPeriod + "'"
rs.Open sProc, conn

Sheet10.Unprotect sPass
Sheet10.Range("A3").CopyFromRecordset rs
Sheet10.Protect sPass

End Sub

My doubt is why this code works for the button if i call from Sheet10.... It does not work if i call this sub from Sheet1....... One more funny issue is the above work for most PC in my compny but the error happens on a few.........

Pls help me. Thks.
Reply With Quote
  #4  
Old 01-12-2009, 03:12 AM
caholmes caholmes is offline Subscript out of range error Windows Vista Subscript out of range error Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default Subscript out of range error

Try selecting each sheet first before you get (refresh/requery) the data. Technically you should not need to do this however I have found this to work for me in past.
Reply With Quote
  #5  
Old 01-12-2009, 06:35 PM
KHTAY KHTAY is offline
Novice
Subscript out of range error
 
Join Date: Jan 2009
Posts: 3
KHTAY is on a distinguished road
Default

Hi caholmes,

I tried the soultion u suggested but the problem still arise...... I have a doubt..... Is this issue related to some settings? Bcos it works on most machine...... Only few not working...

regards
Reply With Quote
  #6  
Old 01-12-2009, 09:16 PM
caholmes caholmes is offline Subscript out of range error Windows Vista Subscript out of range error Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default Subscript out of range error

Are they are the same version of Excel?
Reply With Quote
  #7  
Old 03-28-2009, 11:18 PM
jimmy123 jimmy123 is offline
Novice
 
Join Date: Mar 2009
Posts: 2
jimmy123 is on a distinguished road
Default

This error normally is a result of refering to an object that does not exist or you cannot access. In your case you may find that the users log on credentials don't allow you to access the recordset (datasource). Try each machine to see if you can access the data manually first to rule out a network security issue.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Range names and length stuff. Please help-a-noob. contiw Excel 2 09-06-2007 07:59 AM
Subscript out of range error Mysterious "Subscript out of range" error rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft