+ Reply to Thread
Results 1 to 11 of 11

Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

  1. #1
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    I am looking to create a macro that will look at cells in one workbook and create one code in another workbook. The EFCodeCompare workbook has the information for the codes and the EF-FinalBook has the workbook where I'd like to add two columns (ItemCode and Classification) The item code looks at the EFCodeCompare for the cells in "Spec" and then the "Class" and then the "Size" example 5EE-0450-0020 and pastes it to the corresponding row under ItemCode in EF-FinalBook. It also looks at the "Desc" row and adds the new "Class" Row. I hope these two workbooks clear up what I'm looking for...

    Thank you!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Hi
    I cannot see any relationship between the two Workbooks so how do we know what codes to pick up to generate the new code you are after?
    Tony

  3. #3
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Sorry for the confusion, the row with Spec in "EF-FinalBook" should look for all items in "EFCodeCompare" with the same spec. It then looks at the Desc in the "EF-FinalBook" and the Description in "EFCodeCopare". The Description gives the second part of the code (usually 4 numbers) and also adds the column for the Class column in "EF-FinalBook". The final part of the code is from the NPD column in "EF-FinalBook" and looks at the Size column in the "EFCodeCompare" and gets the code for that. Basically there are 3 parts to the code xxx-xxxx-xxxx. The first is the spec, the second is the description code, and the third is the size code. I can create a flow chart if that would help?

  4. #4
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Hi
    The problem I am having is that there appears to be no logical relationship between the two Workbooks. For example:
    EF-FinalBook : Row 8 : Spec = 6EEP
    There are 11 of these codes in the Compare Workbook - how do I know which one to pick up?
    Also the 4 digit number is in the Code column NOT the Description column!
    And there is no Size against any of the entries of 6EEP.

    Without logical relationships between the two worksheets we are stumped .

    Tony

  5. #5
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Here is a flow diagram of what I am trying to work out...

    ProgramFlow.JPG

  6. #6
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Hi
    I understand what you are trying to achieve however let me see if I can explain the problem more clearly.
    If you take each row in Columns A to D in the ColumnCompare workbook each row would be one record. However in columns E and F the entries there bear no logical relationship to each row in Columns A to D. In fact Rows 24 to 35 in Column E and F have no Size or Code. So the problem remains as to how we link the correct size to the correct record in columns A to D. We can generate a code quite easily but it would be pure guesswork.
    Hope this makes sense.
    Tony

  7. #7
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Thanks Tony,

    I understand...what if I take each section of rows, copy and give it the possible sizes so they have a relationship with the information in the rest of the row? Something like this revision to the sheet?

    EFCodeCompare-Rev1.xlsx

  8. #8
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Hi
    We are getting there .
    In the Code Compare book there is no entry under 6EEP that matches the NPD2 entry of 0.3125 in so a code can never be generated.
    This should explain why there needs to be a match for each field in both Workbooks.
    Tony

  9. #9
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Hi
    Please find herewith an example of how it could work once you have all the correct details in your Workbooks. I am out of the country for a week from tonight and did not want to leave you without a possible solution. Let me know if it provides you with what you need.
    Good luck.
    Tony
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    Tony,

    Thank you so much!! The formula's worked great! The only thing that I am having a problem with now is using them in the macro. I tried using double quotes, but it is still erroring at this line...

    ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(A2,'X:\BOMtoBOQ Macro\[ItemCodeCompare.xlsx]Sheet1'!$A$1:$G$65536,1,FALSE)),""ITEM NOT FOUND"",[@Spec]&"" - ""&VLOOKUP(A2,'X:\BOMtoBOQ Macro\[ItemCodeCompare.xlsx]Sheet1'!$A$1:$E$65536,5,FALSE)&"" - ""&VLOOKUP(A2,'X:\BOMtoBOQ Macro\[ItemCodeCompare.xlsx]Sheet1'!$A$1:$G$65536,7,FALSE))"
    Last edited by michaelpair; 06-25-2013 at 12:36 PM.

  11. #11
    Registered User
    Join Date
    06-01-2013
    Location
    Phoenix, Az
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

    After playing with this one, I found that taking out the R1C1 after ActiveCell.Formula did the trick... Thank you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1