PASTE FORMULA from lookup table

ChrisOK · #10 04-09-2020, 08:54 AM

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

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