Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 04-14-2020, 07:25 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


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.
Reply With Quote
  #17  
Old 04-20-2020, 11:48 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

Hey ChrisOK, is it reasonable to think you have abandoned this both here and at another forum you posted to ?
Reply With Quote
  #18  
Old 04-20-2020, 11:58 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
Default testing

@NoSparks, actually, no it's not abandoned, I was pulled away for other project deadlines and got back to it this -- this morning -- started re-testing and have encountered an error on the 'fm' line of the code -- trying to figure out why it stopped working... I also expanded the lookup table and was going to post and updated working copy for others to use that would handle a larger variety of file types (lengths) - once I get it working again. (I'll try to get it resolved, updated and closed out by EOD if possible) Thank you

Last edited by ChrisOK; 04-20-2020 at 12:01 PM. Reason: typo
Reply With Quote
  #19  
Old 04-20-2020, 12:25 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, @NoSparks,

Encountered issues with a Runtime error "5" Invalid procedure call or argument.
Kept turning yellow on the "fm" line of the lower part of the script -- been battling it all morning in between other work interruptions but was FINALLY able to get the updated file working again -- started from scratch w/ the ("x") file and worked forward again... (new file name is diff so it doesn't overwrite others previously downloaded). This solution is amazing and appreciate everyone's help to fine a solution!

I'm attaching a cleaned up version of the working file (which now includes my expanded lookup table (Columns N&O) you refer to as the "Collection" in the script.

The expanded table will allow a larger variety of file lengths/variations which resolved my issues with so many different lengths going on..
Thanks greatly for your help getting this working again!
Attached Files
File Type: xlsm x_EXPANDED.xlsm (31.9 KB, 8 views)
Reply With Quote
  #20  
Old 04-22-2020, 05:01 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

Always nice to be appreciated, ChrisOK. One thing I don't understand: I suggested (twice) that it might be simpler to use a single formula, using not the MID function but RIGHT and LEFT. You could put that formula in column K, omit the table of formula entirely and not have to write or use any VBA program. Did you have some reason not to want to use that solution, or did you just not see it (twice)?
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 06:27 PM.


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