Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Run time error on list box?

Posted on 2014-04-16
18
152 Views
Last Modified: 2014-04-18
Folks,
I keep getting this error:
Run time errrorThere's no particular pattern
I've attached the file.
I'm to the point of completely re-coding this in a new workbook.
AnalysisToolPakFinancialScientif.xlsm
0
Comment
Question by:Frank Freese
  • 10
  • 6
  • 2
18 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40005954
HI,

Where does the error appear?

Regards
0
 

Author Comment

by:Frank Freese
ID: 40006797
Let me give you an example because it is inconsistent.
When the workbook opens it goes o the first item under category. From there  you can go to whatever category you choose (right now I have only the first category and first topic almost completed. You can go to any sheet by double-clicking on the Topic or clicking on the Go To Selection, Description, or Function Name. That will take you to the desired worksheet - OK to here. Now let's say I wanted to launch the Analysis ToolPak. At that point the error appears as seen below:

Error
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40006931
There's no Data tab and no data to fill the listboxes.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40006962
Hi,

maybe try instead of ListTopic.ListFillRange = "Analysis"

        strAddress = Evaluate("=ADDRESS(ROW(Analysis),COLUMN(Analysis))&"":""&ADDRESS(ROW(Analysis)+ROWS(Analysis)-1,COLUMN(Analysis)+COLUMNS(Analysis)-1)")
        lstTopic.ListFillRange = "Topics!" & strAddress

Open in new window

to see if it evaluates the Named Range correctly

Regards
0
 

Author Comment

by:Frank Freese
ID: 40006966
Martin,
How do I fix this? I've been using the code you provided from the first workbook that's and that's confusing me now.
Thanks,
Frank
0
 

Author Comment

by:Frank Freese
ID: 40006971
Rgonzo1971,
Thanks - I'll look at this
Frank
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40006982
Hmmm. I closed the workbook and then reopened it and the data showed up. Let me see if I can reproduce the error.
0
 

Author Comment

by:Frank Freese
ID: 40007013
Thanks...again, this does appear when I try to load the Analysis ToolPak but then sometimes it does not. When the Analysis ToolPak loads and I fill out the ranges - bingo - error?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40007026
OK I don't have the Analysis Toolpak. Do I install "Analysis Toolpak" or "Analysis Toolpak VBA" or both?
0
 

Author Comment

by:Frank Freese
ID: 40007080
Load the Analysis ToolPak add-in. Not the Analysis ToolPak VBA
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40007165
OK I've done that and I see that it fails once in a while. I'll try to figure out why, but I don't have a lot of confidence that I'll be able to help.
0
 

Author Comment

by:Frank Freese
ID: 40007280
Truly frustrating....thanks Martin
0
 

Author Closing Comment

by:Frank Freese
ID: 40008870
Thank to all...
Rgonzo1971,
I entered your code and tried many times to see if the error reappeared. I closed the workbook, reopened it, tried again and again and NO ERROR. I'll need to better understand what you did but for now thank you
0
 

Author Comment

by:Frank Freese
ID: 40008923
Folks,
Something strange happened in paradise today. Rgonozo1971 code solved the error problem but created a new problem in how the Analysis ToolPak works by returning to the first tab and selecting it's own data set. After I removed the range and went to the second tab I correctly put in the ranges. The problem I got was that the displayed data was not where it was suppose to be.
So I rem out that new code and returned to what I originally had. Now everything seems to be working as expected. The error is gone and the Analysis ToolPak is functioning as designed.
Go figure! Thanks again
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40008950
I thought I posted this already but apparently not. :(

In any case yesterday while I was testing I found that the formula for Named Range 'Analysis' was wrong. It should be

=OFFSET(Topics!$A$1,0,0,COUNTA(Topics!$A:$A),1)

and not

=OFFSET(Topics!$A$1,0,0,COUNTA(Topics!$A:$A) +1 ,1)

When I made that change the wb no longer crashed.

To make that change go to Formulas|Name Manager and edit the formula. Note that most of the others need changing too. There's one that has +10 rather than +1 and I don't know what that is all about but you may either have to remove that +10 or change it to +9.
0
 

Author Comment

by:Frank Freese
ID: 40008961
I replaced your code - I got the same error. I'm wondering if there is something else? I don't know what? Since I am not very far into this I thought I'd recode everything keep only what I need at this time regarding modules.
Before I do that could you send me your workbook that is not crashing please?
Thanks
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40008970
See post ID: 40008950
0
 

Author Comment

by:Frank Freese
ID: 40008991
Let me open another question and start afresh.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question