INDEX & MATCH = "#NAME?" Error.

Hello GC Excel. I tried just like you suggested, and I'm getting:

"=INDEX(TaskDescription[TASK DESCRIPTION],MATCH([CODE],TaskDescription ,0))" AS TEXT ON MY FORMULA CELL.

So I don't think it is a good sloution. Please let me know y you think of something else.

I really appreciate your time.

Thanks FDibbins. I really appreciate it.

Hello GreyGhost. Someone helped me out to fix my post. Please keep my project in mid. I'll be cheking on your posting. Of course, I'll let you know if I can figure it out.
Talk to you soon.

Hi excelV3

I just worked your problem out, using a totally different approach.
And it works perfectly
I have 10 minutes to get to the dentist. I will post you again when I get back.

GreyGhost

I came out with the same solution. It works, half-way do. It keeps repeating the first value throughout the column. However, the same formula on a regular EXCEL CELL works perfect. I'm posting the updated version of my worksheet. Can you please take a look at it?. thanks.

Hi excelV3

Your modified file is attached.
two versions
1. Clean version with expression comments
2. Dirty version containing all the left over crud (expression tests etc) for you to look at if you want to.

All modules are headed with "Option Explicit" which is a must
I have abandoned the use of worksheet functions in VBA, just too clunky and hard enough to follow for anyone.
The form combobox is filled with an array, based on the "Tasks" table.
I have included an eNum statement (instead of numeric constants) to represent each column in the "Scope" table.
New added item details (3 fields) are now added by the OK btn on the form, instead of your previous arrangment.
Would have been problematic if form is cancelled (unloaded) and Item-ID is populated but not the other 2 fields
The combo box contains a 2 column list -- and will auto accommodate any added tasks in the future.

In fact I rewrote the entire project including copious expression notes.
Anything you are not clear on, post back here in this thread
Sorry about the delay (dentist was heavy duty)

Regards,
GreyGhost

FINISHED.zip

Hi Norie

I have couched this reply for the benefit of newbies too, so my respectful acknowledgment to experienced Excel coders.

For the purests I have used the familiar term macro in place of tech-talk procedure

Originally Posted by Norie

Was there some problem caused by subs being public instead of private?

No problems but not good coding practice.

All macros in a project are "remembered" in hierarchical order. If all macros are public, VBA has to look through the list of all macros in the project. But if a macro calls a private-macro on the same module, VBA only has to find the private macro on the module and not look through the entire list of all macros in the project.

• Similar to the tree structure in "Windows Explorer" ...
  ... it is easier to find a file when you know which folder it is in rather than one folder with a zillion files on a massive list.
  With sub folders you can also have files with identical names in different folders

-----
TERMINOLOGY:-
Scope = extent of availability in the project
Public (scope) = project wide, the workbook
Private (scope) = limited to the module
-----
For memory there were 7 project scope Subs on the standard module.

One Public macro was all that was necessary for the sheet button.
Calling other public subs containing one expression hinders the efficient run-times/efficiency of the project.

The slave macros were used as module scope (only called by the button-macro) and should have been "Private" limited to the module.

Conversely one massive macro stuffed with everything is not easy to test either. It is a case of balance.
eg, the "Private Function fnContinue()" in second code-block in post #15 is self contained resolution to a specific single outcome ... also made reading the primary public macro easier to interpret.

Please Login or Register  to view this content.

Project scope (Public) macros on standard modules ...

1. we cannot live without them (almost:- there are exceptions)
2. Increase the file size (not a significant problem on this very small project)
3. with fewer Public macros the project will run faster (not very noticable in a 35KB project, but makes a difference in big projects)
4. Restricts the ability to use the same macro name on other standard modules in the project.
5. With multiple projects open, if all macros in all projects are public the Macros list can be very crowded with macros from other projects.
   Granted other project macros are listed as eg, "[MyBook.xls]mymodule.my_macro"
   It is best practice to only have essential procedures (Subs, Functions) as "Public"
6. Controls on addin toolbars use project scope macros.
   If there are two or more projects open (eg, an xls and xla) files with identically named macros the xla toolbar control will not run any macro.
   XLA macros (session scope) have precedent over an individual XLS (xlsm) project scope macro, so in theory an XLS control can run an invisible XLA macro of the same name.
   Very confusing to the user, because only the xls project macro is listed on the macros dialog, but infact the xla macro is also "available"
   Excel's solution with identical macro names is to do nothing. Which leaves the user in bewilderment, "why doesn't the macro run?".
   (And yet the xls macro will run from the Macros list but not by any sheet controls.
   (In addins I name all public macros with a trailing identifier, eg, "MyToolbarControlMacro_18Jan2013" to save clashing with other projects (xls and xla))
   
   
   Most basic users may never encounter the problem, but it is possible.
   
7. As all public macros on std modules are listed on "Macros" dialog/list any user can run them, and to an inexperienced user the consequences might be catastrophic. (ie, an unhandled error)

=====
Macros can be hidden on the Macros dialog by adding the statement "Option Private Module" at the top of a standard module before any variable/constant declarations
Option Explicit

- a must on all modules (form-class and standard modules)
- VBA does not have to guess at the data type of variables and constants (guessing slows run time)

Option Private Module

- Hides macros on "Macros" dialog, but does not impeded the availability of the macro
- Only on standard modules, never on Classes (userform) otherwise a compile error at run-time.

Please Login or Register  to view this content.

=====

Then there are addins; Class Modules (of which UserForm is a Class); Property procedures; eNums ... and ActiveX controls on worksheets
... fun for another day and place !

GreyGhost