#1
|
|||
|
|||
PASTE FORMULA from lookup table
looking for way to PASTE a formula from a lookup table using either a function -- or using VBA --- (if it can be done both ways, would love to see how it's done for versatility) THANK YOU - (small example attached)
In a nutshell, IF the length of column A file name is 21 chars long, then PASTE SPECIAL FORMULAS the formula found in Cell O$2$ IF the length is is 22 then use the formula in $O$3 (potentially, this lookup table could have more scenarios but for now, just 2) IF LEN IS 21 THEN USE THIS FORMULA, IF FORMULA IS 22 USE OTHER FORMULA |
#2
|
||||
|
||||
Interesting! Off-hand I don't think you can do it outside VBA. But in VBA it should be pretty simple. You didn't say where you want that formula to go, so you'll have to paste in your own range. And I'm used to R1C1 notation (for obvious reasons), so I may not get the A1-style notation just right, but I'm sure you can fix it if I make a mistake:
Code:
Select Case Len(Range("A1").Value) 'or wherever the filename is Case 21: vf = Range("$O$2").Value Case 22: vf = Range("$O$3").Value Case Else: 'you didn't say what in this case End Select Range(target range).Formula = vf That's assuming that in $O$2 and $O$3 you have the formulae in quotes. If you have the actual formulae, I guess it would look like this: Code:
Select Case Len(Range("A1").Value) 'or wherever the filename is Case 21: vf = Range("$O$2").Formula Case 22: vf = Range("$O$3").Formula Case Else: 'you didn't say what in this case End Select Range(target range).Formula = vf I haven't tested this code, but it's how I would attempt it. |
#3
|
|||
|
|||
Not working
I'm not sure if it needs to change to the R1C1 format or what?
I tried "A1" and no luck, Also tried A1:A17 range - still getting error Can you take a look at NEW attachment (saved as xlsm) Not sure how to put it in the R1C1 format Also, I'm trying to get the result to appear in column K currently for the test But will then move it into col B (so the formula in B will adjust based on the length of the filename in col A |
#4
|
|||
|
|||
I updated the test file again just now to
"A2" only (for the Len Range) and then changed O2 to NOT have quotes on either side of the formula and it pasted the result of the formula (not the formula) If I put the quotes back, it pastes the formula with quotes and therefore does not function as a formula |
#5
|
|||
|
|||
and I'm seeing another potential issue... after removing the quotes from the lookup table in the 2 formulas in col O, then re-ran it, ---- those 2 formulas need to be relative to the cell at the left it's looking at.... (COL A)
So, if it gets down to ROW 10, those filesnames are longer in LEN and need to use a formula that would read: =MID(A10,14,5) Currently, the formula is col O is looking to A2 In other words, IF A2 is 21 chars long use the formula in O2 If A2 is 22 chars long use the form in O3 BUT as it pastes down within col K it needs to be relative A2 A3 A4 A5 and when it gets to row 10 it will know to adjust to A10 |
#6
|
||||
|
||||
Now that I look at your worksheet I see I misunderstood your request. This is one reason I grouse about A1 format: A poster says "A2", and I can't tell whether he means that row particularly or just "the current row". We need a convention like "Ar" (or something) to mean "the current row, column A".
R1C1 is better. But I don't really hope, realistically speaking, to get everyone used to saying "RC1" instead of "$Ar". It is much less ambiguous, though. Back to your question: Now that I see the actual formulae over in column O, I realize what you're trying to do. In that case, I'm still sure you can do it with VBA and "remote" formulae—to coin a term—but it's easier this way: In column K try "=LEFT(RIGHT(A2,9),5)" (without the quotes). You don't need to check the length of the name in column A; all you need, unless there's more variation in the filenames than shows here, is to take the rightmost nine characters of the filename, and then the leftmost five characters of the result. If you need the results to be displayed as a number instead of a character string, just add 0 ("=LEFT(RIGHT(A2,9),5)+0"). Copy that formula down col K and see whether it isn't what you want. Do you still want to do it in VBA, or at least to know how? If so, we can work on that too. It would be useful, if some filenames differ from the pattern I see here. |
#7
|
|||
|
|||
I've attached an updated version of the test file to show the outcome of the last suggestion -- and provided explanation in the blue message block..
That 1st formula is better it seems than the one I had, but still think a VBA solution using the IF THEN route w/ 5 to 10 rule variations will likely be the most dynamic and dependable...(with the least amount of manual edits) when a variety of file names from different mainframe systems gets pasted into Col A (see attachment to see what I mean). Alternatively, if there's a way to use VBA to use that lookup table to lookup the matching LEN in col N and then PASTE the formula found in col O into col K, then the user could quickly add variations without having to go into the backside to add lines of code. I'm find with either method, whichever is easier -- as I'll be the one using it most -- but was thinking ahead -- in the event I had to pass this to a client to use on their own who didn't know anything at all about VBA... (I'm not an expert by any means - but can do basic things) Thanks again for your help! Look forward to seeing if those 2 VBA options might work? |
#8
|
|||
|
|||
VBA and no lookup table
Code:
Sub Test_1() Dim rng As Range, cel As Range Dim startpoint As Long With Sheets("51batch") Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For Each cel In rng startpoint = InStrRev(cel, "_") + 2 'for test purposes write formula to column "K" cel.Offset(, 10) = "=Mid(" & cel.Address & "," & startpoint & ", 5)" Next cel End With End Sub Consulting my buddy Google lead to this array entered formula without any vba =MID(A2,FIND("~",SUBSTITUTE(A2,"_","~",(LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))/LEN("_")))+2,5) I must confess I don't understand it but the establishing of where to find the last underscore comes from an old posting by the very knowledgeable Tom Ogilvy Last edited by NoSparks; 04-08-2020 at 11:39 AM. Reason: to add the formula part |
#9
|
||||
|
||||
A VBA solution is certainly possible—and a lot more fun —but I'd need to know what variations you think the filenames might exhibit before I can come up with an algorithm. What else might the filename look like, and which part do you want to extract in each case?
And I see your point about the user being able to enter a table of possible filename types and associated formulae—more flexible that way. I'll try to take a look at that tonight (since my original program didn't work for you) and make a program that behaves correctly. |
#10
|
|||
|
|||
vba lookup match paste adjacent
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 |
#11
|
||||
|
||||
Ok, I made one that actually works. In column O you put the various formulae the user wants to be applied, with a single quote in front so that Excel will know to treat that formula as the displayed value of the cell rather than a formula itself. And wherever there's a reference to the cell that has the filename, for example "A2", replace it with some unique text. I used "ZZ". Like this:
Code:
'=MID(ZZ,13,5) Code:
ows.Cells(jr, 11).Formula = Replace(ows.Cells(vr, 15).Value, "ZZ", "A" & jr) |
#12
|
|||
|
|||
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.. |
#13
|
||||
|
||||
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) 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. |
#14
|
||||
|
||||
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!".
|
#15
|
|||
|
|||
@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. |
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 |