Thanks @BobBridges Can you expand upon how the new script would look?


Not sure how to set it up..

I've added the formula into the lookup table cells for the script to look to -- and started playing around w/ a new "MODULE 4" (in the attached)- but not sure how the full script should look to interactively work w/ it..

Attached Files

test-paste-formula_2020-0413.xlsm (47.6 KB, 9 views)

It's probably easiest for you, and for me too, to work on your example and upload my own solution here. Meanwhile, while I'm working on that: I mentioned before that a simpler solution might be to ignore the file length. In each case, it seems to me, your file names are a bunch of characters (of varying length), followed by "nnnnn.TXT". And what you want in each case is the nnnnn part. That being the case, why worry about the length of the filename? Just take the five numeric characters that come before ".TXT", like this:

Code:

=LEFT(RIGHT(A2,9),5)

That one formula works no matter how long your filename is; no need for a VBA program at all.

I mentioned this before and you said there are other filename variations for which that wouldn't work. But I don't see any exceptions in your sample worksheets; I suspect you just didn't notice how the above formula works. Take another look.

Of course it's possible there will be other variations that you haven't told me about, or maybe even you're just keeping in mind that you cannot know what variations may come up and you'd rather stay flexible. But in that latter case it seems to me you have no guarantee that filename length will be the right determinant. I'm very distrustful of assuming this is all going to go on working forever using filename length. Can you tell me more about the possible variations? There are other approaches.

See how this works for you. I threw in some explanations, which may or may not help. The point, of course, is not a program that works for you, but one you can look at and say "oh, now I understand how to do that!".

Attached Files

x.xlsm (61.7 KB, 11 views)

@BobBridges Thank you! Yes, it works this time around! Looks like a lot more script was needed that I definitely will need to study in the morning bc it looks pretty complex!

I wanted you to know, I did have the preceding quote in there initially (I think on the 53 batch tab) but, when I saw it didn't give the desired result all the way through, I took it back out -- to demonstrate how -- if the wrong formula was copy/paste applied, then the Julian picked up would be inaccurate. I guess I should have left a sheet in tact showing it was there at one point =-(

Heading to bed and will study the details in the morning to walk through the steps and will let you know if I'm stuck on the acquiring wisdom part - which you're right -- LEARNING something from the pain is important and certainly very much appreciated.

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

Even though Excel often assumes the parents of the objects I refer to, and usually its defaults are what I wanted, every so often they aren't and I have to spend some minutes trying to figure out why the program isn't doing what I meant it to. Eventually I just got in the habit of writing it all out every time. You don't have to...until you've been programming for years :-).

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.