+ Reply to Thread
Results 1 to 3 of 3

vba to Match concatenated values in concatenated columns

  1. #1
    Registered User
    Join Date
    02-27-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    2

    vba to Match concatenated values in concatenated columns

    Being new to vba, I have written the following code which copies a formula into a cell. Although this works, it is extreamly slow being I repeat this for several other values.

    Please Login or Register  to view this content.
    This formula concatenates the SO_number & Full Item that is in column K of the current sheet and looks for the resulting value in the SO sheet by concatenating column B and E.

    the SO sheet contains information from Sales Orders. Sheet1 is the final product that contains information from other sheets (POs for example) and related information from the SO sheet.


    Here is some sample data:
    SO
    Please Login or Register  to view this content.
    Sheet1
    Please Login or Register  to view this content.
    I would like to replace the formula with vba code that finds the row with the desired information and then use the Row number for the other values that I need. This way, I am only looking up the data once per row and the final sheet only contains the values (rather than the formulas).

    Any guidance would be appreciated.

    Thanks

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: vba to Match concatenated values in concatenated columns

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
    To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.

    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file.

    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  3. #3
    Registered User
    Join Date
    02-27-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: vba to Match concatenated values in concatenated columns

    OK, I didn't go into too much detail just in case it was a quick thing. I have attached a sample workbook that has a subset of the exact data from my workbook. Sheet1 is the output and contains the cells that have the issue (in BLUE).

    The 2 other sheets contain Purchase Order (PO) and Sales Order (SO) information that is pulled from our accounting system via ODBC. In the real workbook version, it is a dynamic table but I've only provided you with a subset as I'm sure you don't want a huge pile of records.

    OK, so what's going on.

    Sheet1 data OUTSIDE of the BLUE is already taken care of and for the purpose of this question, is a given. The BLUE section is what we are trying to create. Currently, I am copying formulas into these cells (via VBA) to do the lookup of information but would prefer to do the lookup in VBA code and place only the VALUES in the cells as it will be much faster.

    Right now the formula in D2 on Sheet1 looks like this.

    Please Login or Register  to view this content.
    So, this formula is taking the SO_number (2537) concatenated with the data in cell K2 and using this value to lookup the correct row in the table on the SO sheet (SO!$B$2:$B$1000&SO!$E$2:$E$1000). It then returns the corresponding Description from the SO table.

    The other cells in the BLUE section work basically the same way but return other related data from the same row ( QTY, Rate, Amount).

    I'm looking to do this concatenated search in VBA and place only the values in the cells. If possible, using dynamic table references (or if you have a better idea) to get away from the SO!$B$2:$B$1000&SO!$E$2:$E$1000 type reference. I'm not sure how to make that work when using concatenation.

    The original VBA code that places the above formula in the cell is located in the first post.

    Hope this helps.
    Attached Files Attached Files

+ 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