I wrote a program in Excel 2010 and it works fine, but this code will not work in 2003. I did check the object references in vba and unchecked the Missing ones, which was Outlook. I am getting errors such as "Can't find project or Library" and Mismatch type among others. It all seems to circle around the 2 lines of code that I highlighted below in red.
Please Login or Register to view this content.
Last edited by Mak2145; 03-28-2012 at 11:47 PM.
If the upper range of Range("SUB_OPS_USED") is more than 65,000 then it could be it. ALso you are not using any declarations. Try declaring c as a variant and see what happens.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Actually, declare C as Range, since that's what you're using it for.
Sup_Ops_Used is 120. I declared C as Range and got a type mismatch on the "If C.Value = "YES" Then" line
What is the exact range of SUB_OPS_USED? If it's to the left of column C I think you'll have an issue as you're offsetting -2. Longshot.. but try getting rid of the word SUB in your variables and ranges.
If not, can you post a copy of your workbook so someone else with Excel 2003 can look? (Not me, unfortunately..)
Here is a basic version
For 2007+ you may want to use the code like this:
All variables are Dim'd, and Range objects are qualified to their proper worksheets. What might be the issue in 2003 is the Sort part of the code. Try recording a macro of sorting a column in Excel 2003 and you'll see what the code should be. (It's different - and I believe you can use the 2003 Sort code in 2007+, but not vice-versa.)Please Login or Register to view this content.
Also note that your sorting starts in row 18 (A18), however the data starts in A2. So the first 16 rows don't get sorted with the rest of the data.
Check the sort code part, have you debugged it in 2003. The sort method is different in Excel 2003, try recording the same actions & compare the code.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I recorded the sort and replaced. Still no go. It still errors to the "If C.Value = "YES" Then" line. Also Paul, yep I was minimizing the file and forgot to change the range from A18. No big deal tho
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Hi Roy, the OP provided one in post #6. It runs fine with Paul's code. I will try to run it on a laptop at work that runs 2003 and try to debug.
I posted an example a few posts up.
Since we need to test in 2003, can you re-post a copy of the file in .xls format? I know there are converters for reading newer files in 2003, but we should probably start with a clean slate.
Hi Mak2145
Change your Sort RoutineAlso, you could probably use AutoFilter for the first part of the routine rather than a loop and VLookUps. Let me know of issues.Please Login or Register to view this content.
Last edited by jaslake; 03-29-2012 at 02:04 PM.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
If you're getting a type mismatch on a cell value test I'd bet you have an error in that cell.
Thanks for all of the help. The problem is I didn't know IFERROR statments don't work in 2003. I have found the macro to let them run in 2003, but I am having a slight problem. All of the iferror statements have a _xlfn. attached to them now. I can do a manual find and remove and the program runs fine. I am trying to create a macro to do this for me, but the problem is the macro won't search formulas like doing it manually can. I have this base code, but am not sure what do add to accomplish this.
Please Login or Register to view this content.
You can't simply remove the xlfn part as iferror will not work in 2003. You need to switch to if(iserror( formulas instead. You would find it easier to build the project in 2003 I reckon.
I am using this macro for the iferrors to run in 2003.Originally Posted by JosephP
If I do a manual find and remove the xlfn, this works great and the program runs fine after that. I just need a macro to automatically find and replace the _xlfn. within the formula.Please Login or Register to view this content.
Your code works fine in your sample workbook in 2003 - replace will only work on formulas since you can't replace the result of a formula without changing the formula. I still think it would be simpler to use "IF(ISERROR(" but I don't see why you can't do the replace manually once anyway.
That example book i didnt include any of the iferror statements. I originally designed this in excel 2010. I completed it and found there are still a couple of users with 2003 that need this program to run on thier computer. The reason i need it to be automatic is because i will be distributing this to different people and most are very basic units.
The workbook in post 6 does have IFERROR formulas in it.
Why don't you just replace the formulas before distributing it - then it will work for everyone.
Hi Mak2145
As I previously suggestedThis code does that and eliminates the lookups. It's been tested in Excel 2007 and Excel 2000 and appears to do as you require.you could probably use AutoFilter for the first part of the routine rather than a loop and VLookUpsPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks