Thanks NoSparks and Bob for more ideas - but they still don't resolve the issue..
I think the only way is to do the orig lookup table route (I've attached an UPDATED SAMPLE file to show what happens when I tried out the new options and better visual of why the lookup would work -- expanded)
Shown below, is the MODULE 3 script that has the general idea of what I need to happen, but it's not accurate/dynamic enough to Lookup, Match, then Paste Special Formula the adjacent cell from Col O into Col K. Currently, it just takes whatever is in A2 and applies it all the way down Col K
*Col A filenames from the various mainframe systems will vary both in LENgth and in format... so, we can't depend upon there always being 2 underscores, etc..
I've altered this new updated attachment to test some random format variations..
None of that should matter.. only thing that does matter is what is the LEN of Col A, and based on that, go find it's match in Col N, then paste whatever formula is sitting adjacently in Col O.
If the VBA can calculate the LEN on the fly then Col B won't be needed.. but if easier, it's there to use to bounce against Col N to look for a match..
Code:
Sub PasteFormulaExpanded()
Select Case Len(Range("A2").Value) 'or whatever filename is
Case 14: vf = Range("O$2").Value
Case 15: vf = Range("O$3").Value
Case 16: vf = Range("O$4").Value
Case 17: vf = Range("O$5").Value
Case 18: vf = Range("O$6").Value
Case 19: vf = Range("O$7").Value
Case 20: vf = Range("O$8").Value
Case 21: vf = Range("O$9").Value
Case 22: vf = Range("O$10").Value
Case Else: vf = Range("O$11").Value
End Select
Range("K2:K39").Formula = vf
End Sub