Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2020, 12:51 PM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question 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
Attached Files
File Type: xlsx test-paste-formula.xlsx (15.5 KB, 8 views)
Reply With Quote
  #2  
Old 04-07-2020, 01:20 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
If you write the formulae in R1C1, the property is FormulaR1C1.

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
But in that case you might get into issues with addressing; you'd have to remember to make the formulae in column O point to the correct rows and columns after coping them to your target range.

I haven't tested this code, but it's how I would attempt it.
Reply With Quote
  #3  
Old 04-07-2020, 01:46 PM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question 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
Attached Files
File Type: xlsm test-paste-formula.xlsm (20.7 KB, 7 views)
Reply With Quote
  #4  
Old 04-07-2020, 01:52 PM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question

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
Reply With Quote
  #5  
Old 04-07-2020, 02:00 PM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 04-07-2020, 08:11 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #7  
Old 04-08-2020, 06:22 AM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question

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?
Attached Files
File Type: xlsm test-paste-formula.xlsm (21.7 KB, 6 views)
Reply With Quote
  #8  
Old 04-08-2020, 08:38 AM
NoSparks NoSparks is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Assuming that works...
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
Reply With Quote
  #9  
Old 04-08-2020, 12:11 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #10  
Old 04-09-2020, 08:54 AM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question 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
Attached Files
File Type: xlsm test-paste-formula.xlsm (48.2 KB, 8 views)
Reply With Quote
  #11  
Old 04-09-2020, 07:12 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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)
That's for the formula. Your VBA statement gets the value from the correct cell in the table—you already know how to do that, I guess—and then uses the Replace function to change "ZZ" to "A2"-or-whatever, and assigns to the formula of the cell in column K. Like this, perhaps:
Code:
ows.Cells(jr, 11).Formula = Replace(ows.Cells(vr, 15).Value, "ZZ", "A" & jr)
...where jr is the row of the filename you're evaluating, and vr is the row number of the correct formula corresponding to the length of the filename.
Reply With Quote
  #12  
Old 04-13-2020, 09:30 AM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Question

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
File Type: xlsm test-paste-formula_2020-0413.xlsm (47.6 KB, 7 views)
Reply With Quote
  #13  
Old 04-13-2020, 07:04 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #14  
Old 04-13-2020, 07:49 PM
BobBridges's Avatar
BobBridges BobBridges is offline PASTE FORMULA from lookup table Windows 7 64bit PASTE FORMULA from lookup table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
File Type: xlsm x.xlsm (61.7 KB, 9 views)
Reply With Quote
  #15  
Old 04-13-2020, 09:21 PM
ChrisOK ChrisOK is offline PASTE FORMULA from lookup table Windows 10 PASTE FORMULA from lookup table Office 2019
Advanced Beginner
PASTE FORMULA from lookup table
 
Join Date: Sep 2016
Posts: 54
ChrisOK is on a distinguished road
Thumbs up

@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.
Reply With Quote
Reply

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
PASTE FORMULA from lookup table Lookup / Paste Multi Values VBA Mav Excel Programming 6 05-10-2015 08:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft