In the attached workbook I am getting a "SUBSCRIPT OUT OF RANGE" error on the following line.
The data is present in 'ref' however, the variant clears out at some point with no explanation. Here is complete code as well.
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by lloydgodin; 03-06-2013 at 12:06 PM. Reason: corrected attachment
I get a Select method of range... error not subscript out of range.
If I change the loop to this, no error...
Please Login or Register to view this content.
If posting code please use code tags, see here.
I am not even getting the error anymore. I'm not sure why or where...
And here is the corrected attachment.
And now I AM getting the same error that you did.
When I changed to your code I get the SUBSCRIPT OUT OF RANGE error again.
Since 'ref' is contained in an add-in I thought the section of code causing the problem might be this:
Other sections that reference it work.Please Login or Register to view this content.
Did you try the change I suggested?
Yes. And I get the subscipt out of range error still.
The add-in cannot be attached but it is a file that contains the above code in a module, the tab REF and the following code in ThisWorkbook:
Please Login or Register to view this content.
It probably is some sort of reference problem that's causing the error(s).
Hard to tell/test though without the add-in.
The site wont let me upload it. However, it can be created with the 11:26 post and last attachment has the "REF" tab.
Sorry, 11:26 post?
Could you post it's number?
PS Are the 2 files you uploaded different?
Post #8. 11:26 was the time on it b/c I didn't see the post numbers.
The file in post #3 contains the REF tab whereas post 1 did not.
Post #8 was at 5:26 pm GMT.
I'll have a look at the file later.
You say it's an addin?
Do you have a sample workbook to work with it, or will a blank one do?
Different time zones, LOL.
Yes the ResetTest.xls is the sample workbook and using the code from previous posts and the REF tab from ResetTest you can create the add-in. It more accurately reflects how I am doing things on this end.
So what should I use as a sample workbook?
DOesn't matter what you put into as long as you insert lines at 13 and fill it with garbage data. And where it says FACS# put something else is.the ResetTest.xls is the sample workbook
Threadbump
I think it has to do with the variant 'n.' It's running it 101 times and after the data is empty it'll give the subscript out of range error.
bumping this again.
Hi Lloyd,
I was playing with it too and this worked for me:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Thank XL. I'm actually in the office (Camarillo, CA) and the files are back in Texas on my computer. I will try it when I get back home and let you know. Thank you again.
You're welcome!
Nothing I am doing is working. So I zipped the addin, as well as an actual spreadsheet it would be used on.
Last edited by lloydgodin; 04-10-2013 at 08:03 AM. Reason: removed attachments.
Hi Lloyd,
I altered the add in code a little and it ran - see if it's good enough:
Please Login or Register to view this content.
If you could highlight the changes and explain why that would be helpful to me. It is working perfectly. Thank you.
Hi Lloyd,
I noticed that it was choking when the ref was null - so I just told it to ignore null ref records:
Oh! and since the code uses the select feature for the range I Activated the Sheet.Please Login or Register to view this content.
Last edited by xladept; 04-10-2013 at 01:31 PM.
OK. I can see that now. Not sure why it was returning null references though.
Also what did the CStr() do?
I'm pretty new to programming and hardly use what I've learned.
Hi Lloyd,
That's a pretty sophisticate system for a newcomer - the CStr renders the worksheet name reference into a string and it's necessary because worksheet names are strings
Last edited by xladept; 04-10-2013 at 09:52 PM.
Well I have had help from several people on here to get it to do what I want it to do.
I did notice 1 error a week later. There are other workbooks that contain several worksheets (as opposed to the ER which contains just "emergency room"). The bill analysis clears ALL worksheets in a single workbook. Not really a problem except it only clears them and does not do the final step of putting "FACS#" into the cell and selecting the same. It only does that on the worksheet it's used on.
Hi LLoyd,
Can you post the code or, even better, a sample spreadsheet with the fault??
I figured it out. The code you had provided went through the whole array n=lBound to uBound and used any worksheet name data contained in vData(n,2). All 100 worksheets. So I replaced
withPlease Login or Register to view this content.
Where wks = Activesheet so it only runs the code if the sheet is Active which is what I needed it to do.Please Login or Register to view this content.
Good! - Glad you got it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks