LinkBack URL
About LinkBacks
I have the following code:
I'd like to use Select Case but am unsure how to set it up using InStr(). Any help is appreciated.
Please Login or Register to view this content.
I have the following code:
I'd like to use Select Case but am unsure how to set it up using InStr(). Any help is appreciated.Please Login or Register to view this content.
why do you want to use select case? I can't see any benefit from it but you would need to use
Please Login or Register to view this content.
Josie
if at first you don't succeed try doing it the way your wife told you to
There are several workbooks that will be created and I have to keep adding to this list. The location of the specific cell changes periodically due to formatting issues. Trying to clean up the code and not have a bazillion nested If/Thens.
then you ought to use a reference table that you can loop through in my opinion. select case will be just as untidy as if..elseif
Fair Enough.
Can you provide an example of a reference table & loop?
I am not a programmer by nature. Taught myself a few things, learn stuff from people on here, but not very good at it. Nor is it consistent. Any help is appreciated.
assuming a three column table on sheet Ref with the name criterion in column 1, the output value in column 2 and the output cell in column 3
Please Login or Register to view this content.
The code in question is in an Add-in I created. I have been unsuccessful in getting the table onto a worksheet within it.
Create a worksheet named Ref in the add-in, add your data there as Joseph suggested, add a named range to reference it, and put this code (modestly adapted from Joseph's, and completly untested) in the add-in:
Please Login or Register to view this content.
Last edited by shg; 10-09-2012 at 10:36 AM.
Entia non sunt multiplicanda sine necessitate
I know how to add a worksheet except everything is greyed out. I simply am unable to do so. No idea why either.
Select the add-in's VBA project in the Project Explorer Window, select the ThisWorkbook module, in the Properties window set the IsAddin property to False, and proceed from there. Set it back to True when you're done.
Done & Done.
However, there are two issues.
On his code I get a run time error 9: subscript out of range.
Your code is getting a run time error 1004: application defined or object defined error. On this line:Please Login or Register to view this content.
Please Login or Register to view this content.
Lloyd, if the code is in the add-in, and worksheet "Ref" is in the add-in, and Sheets("Ref") refers to the active workbook (which can NEVER be an add-in), which of those codes do you think is correct?
Did you create the named range "ref_table"?
Obviously the one that refers "Ref" being in the add-in.
I did but I may have misentered the scope whilst doing so. I'll have to check it on the morrow.
Note the change in the code in the prior post:
Please Login or Register to view this content.
It's working now. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks