#16
|
||||
|
||||
Yeah, sorry about the complications. Part of the problem is that I've been doing this for a while, so some little extras that I used to skip ("too much trouble for this one little task") I eventually got into the habit of doing just because so often I later wished I had from the beginning. Like loading the table of formulae into a collection before applying them: When I was starting out no doubt I would have figure "Oh, there are only two or three different formulae, I may as well hard-code them into the program". Now, with experience, I agree with you that it's better to be flexible right from the start and leave room to add more as the need comes up—and now I just load the table into a table without asking myself whether I should, just because so often I need to in the end anyway. I also am in the habit of specifying things like the workbook and worksheet: Code:
Set owb = ThisWorkbook Set ows = owb.ActiveSheet And after a while I wrote some subroutines to save routine coding. I have a FetchWorksheet routine, for example, a function that I hand a workbook and a sheet name and it returns the worksheet—but if the worksheet doesn't exist (like if I forgot to create it, or spelled it wrong) the program prints a message instead of just bombing. An Exists function that returns True or False for a particular item in a collection. And so on. I tried not to burden you with that kind of junk; you can figure out for yourself when you want to write it. But the habits make my programs look more complicated than perhaps they need to be for your purposes. |
#17
|
|||
|
|||
Hey ChrisOK, is it reasonable to think you have abandoned this both here and at another forum you posted to ?
|
#18
|
|||
|
|||
testing
@NoSparks, actually, no it's not abandoned, I was pulled away for other project deadlines and got back to it this -- this morning -- started re-testing and have encountered an error on the 'fm' line of the code -- trying to figure out why it stopped working... I also expanded the lookup table and was going to post and updated working copy for others to use that would handle a larger variety of file types (lengths) - once I get it working again. (I'll try to get it resolved, updated and closed out by EOD if possible) Thank you
Last edited by ChrisOK; 04-20-2020 at 12:01 PM. Reason: typo |
#19
|
|||
|
|||
@BobBridges, @NoSparks,
Encountered issues with a Runtime error "5" Invalid procedure call or argument. Kept turning yellow on the "fm" line of the lower part of the script -- been battling it all morning in between other work interruptions but was FINALLY able to get the updated file working again -- started from scratch w/ the ("x") file and worked forward again... (new file name is diff so it doesn't overwrite others previously downloaded). This solution is amazing and appreciate everyone's help to fine a solution! I'm attaching a cleaned up version of the working file (which now includes my expanded lookup table (Columns N&O) you refer to as the "Collection" in the script. The expanded table will allow a larger variety of file lengths/variations which resolved my issues with so many different lengths going on.. Thanks greatly for your help getting this working again! |
#20
|
||||
|
||||
Always nice to be appreciated, ChrisOK. One thing I don't understand: I suggested (twice) that it might be simpler to use a single formula, using not the MID function but RIGHT and LEFT. You could put that formula in column K, omit the table of formula entirely and not have to write or use any VBA program. Did you have some reason not to want to use that solution, or did you just not see it (twice)?
|
Tags |
paste formula lookup |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
LOOKUP Formula error but WHY??? | Haga | Excel | 1 | 04-17-2019 11:13 PM |
N/A Value in Lookup Formula | NickFazer | Excel | 2 | 04-02-2019 04:22 AM |
How to update lastname with the help of V-Lookup formula? | Mangesh1212 | Excel | 6 | 12-05-2018 02:15 AM |
Need help with lookup formula | tristanlau | Excel | 1 | 08-14-2017 07:16 AM |
Lookup / Paste Multi Values VBA | Mav | Excel Programming | 6 | 05-10-2015 08:42 PM |