Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard network printer problem ram registry router security slow software sound toshiba trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: EXCEL 2007 VBA Help

Reply  
Thread Tools
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
03-Nov-2009, 07:08 PM #1
Solved: EXCEL 2007 VBA Help
I need some help with building a macro. I have attached a sample file for reference. What I need to do is pull info from one worksheet to another worksheet. In my wildest dreams I would like to type in the order number found in the reference material under the tab titled Indentured BOM into the "As Proposed Part Number" column found in the BOM1 tab and have the unit cost automatically populate with the corresponding Unit Cost found in the Indentured BOM tab. How can I do this?
Attached Files
File Type: xls Test Data.xls (45.5 KB, 29 views)
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Nov-2009, 10:38 PM #2
Why do you need VBA? Can't you use a VLOOKUP() formula? I.e. in C14 enter...
Code:
=VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0)
Of course you'd have to take into account something not being found, which you could do with a little error trapping in your formula...
Code:
=IF(ISNA(VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0)),"NOT FOUND",VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0))
And just change the "NOT FOUND" to whatever you want to show if the part number isn't seen on the other sheet.

Of coures we could do this with VBA as well, but it wouldn't be as efficient.

HTH
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
04-Nov-2009, 07:37 AM #3
This sounds like an Access application.
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 01:12 PM #4
Vlookup would not work for my purposes. The reason behind setting up a macro is to alleviate the repitition of setting up a vlookup statement. I would like to build a "smart" spreadhseet that would have macros embedded in key cells and triggered by a change event to call a macro. So I type in the order number and whamo the unit cost automatically appears.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 01:15 PM #5
You can do that if you want, but why would you be setting up a formula every time? You only have to put them in once, then you can copy them down as needed. I don't think I fully understand the scope of what you're trying to do here. Can you explain how you're wanting to use your spreadsheet?
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 01:24 PM #6
No problem Zack. On working on a proposal for a bill of material, requirements are constantly changing. In 1 proposal we could have several bills of material that roll up to 1 top assembly. What I have to do is pull the part number/order number for 1 bill of material and so on from the overall indentured bill of material and populate the cost. On any given proposal I will have to re-price and reconfigure the indentured bill of material dozens of times. So if I had a fixed vlookup formula, I would constantly be fixing ranges and I might as well just do a simple "find" than mess with a formula. If you think a Vlookup could work, then I'm game to see how it could fit my requirement. Thanks!
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 01:28 PM #7
Hmm, well with the data range changing that may not work. Do you always have the same headers? And are these different workbooks, or data put into the same workbook? A couple of real-world examples would go a long way, especially if they're different and show the entire contrast of the data structure you have and what you're trying to accomplish.
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 01:37 PM #8
If you take a look at my first post, I have attached an example of what the 2 spreadsheets I work with look like. The headers never change and they are always in the same workbook.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 01:49 PM #9
Okay, so the workbook in this thread is what you're working with. Do you want to do the lookup based on the value in col A or col B of the BOM1 worksheet? And will the start row always be 14? Are you wanting to re-populate the data everytime? If not, how would you know to exclude it? Also, on the BOM1 sheet your headers to do not match that of the headers on the Indentured BOM sheet, you have them broken up into two rows (which I highly discourage). For this to work well, you'll need them to match exactly. Can you do that?
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 01:55 PM #10
Zack, in BOM1 the lookup value should be based on column B and yes the start row will always be 14. Also, I will have to re-populate the data everytime. In terms of the sheet headers matching, I added the headers myself in the Indenture BOM tab, so these headers are flexible and can change to match the one's found in the BOM1 tab. Unfortunately the headers in the BOM1 tab are static and cannot change. Thanks!
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 02:02 PM #11
Ok, well as long as they match and are unique. You really should have them on one row though. Having them on two rows (IMHO) is tacky. Plus it makes writing logic for it more difficult. So what will the Indentured BOM sheet look like then? Are you going to change the structure of the data to have a 2-row header?
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 02:07 PM #12
Unfortunately the format of the BOM1 spreadsheet was designed to meet strict federal guidelines, hence the tackiness. I updated the headers to reflect each other. Let me know if this works. Same file that I started with in my first posting.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 02:22 PM #13
I don't see any changes. The QTY columns on the BOM1 sheet still show two types, whereas the other sheet shows only one. The only one that somewhat matches is the Description, but even that's not exactly the same as it's all caps on the BOM1 sheet and in proper case on the other. Go ahead and re-post the spreadsheet with your changes (in a new post).
Regulator's Avatar
Computer Specs
Junior Member with 14 posts.
 
Join Date: Nov 2009
Experience: Intermediate
04-Nov-2009, 02:40 PM #14
Updated File
Zack, do I need headers on the other columns in the Indentured BOM sheet? Honestly, I never use the info from these columns so I would not know what to call them. Here's the updated sheet.
Attached Files
File Type: xls Test Data.xls (46.0 KB, 24 views)
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Nov-2009, 03:17 PM #15
What's going on with the blank row (3) on the Indentured BOM sheet? Is it there for a reason? What do you want to do if there is no match? Show no data?
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 11:47 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.