Solved

Match Values and Return to Cell

Posted on 2016-10-19
6
42 Views
Last Modified: 2016-10-21
See attached:

I need to lookup the values of Range C2:C5, then return values of Range A8:D8, for each instance. I have hundreds of columns, and rows. The rows and columns will change from file to file. I'd like to be able to find a way to do this with VBA.

as shown on attachment.
C--Users-SZYYNR-Documents-TESTMATCH.xlsx
0
Comment
Question by:MATO0618
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41850805
The following code will transpose and copy the values in the columns in rows 2:5 and their respective PROD and AMT values in rows 8:10 (or further down, at least as far as row 19).  It will clear any data in the OUTPUT section (bound on upper left by cell A24) before pasting in values.  I've added an additional "brand" on top to test for extensibility.  You can have brands as far as column CA in this example.

Option Explicit
Sub Transfer_Data()
    Dim intMods, intProdCats As Integer
    Dim rngMod, rngProd As Range
    Dim lngDestRow As Long
    
    Application.ScreenUpdating = False
    
    'Clear output area
    Range("A24:G10000").Clear
    
    'set number of mods (in columns on top) and PROD categories (in rows)
    intMods = WorksheetFunction.CountA(Range("C2:CA2"))
    intProdCats = WorksheetFunction.CountA(Range("A8:A19"))
    lngDestRow = 24
    
    Range("C2").Select
    Do Until ActiveCell.Value = ""
        'copy/transpose mod values
        Set rngMod = Range(ActiveCell, ActiveCell.Offset(3, 0))
        rngMod.Copy
        Range("A" & lngDestRow & ":A" & lngDestRow + intProdCats - 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        rngMod.Select
        
        'copy over Prod values
        Set rngProd = Union(Range("A8:B" & 7 + intProdCats), _
            Range(ActiveCell.Offset(6, 0), ActiveCell.Offset(5 + intProdCats, 0)))
        rngProd.Copy
        Range("E" & lngDestRow).PasteSpecial Paste:=xlPasteValues
        lngDestRow = lngDestRow + intProdCats
        rngMod.Select
        
        'move to next mod column
        ActiveCell.Offset(0, 1).Select
    Loop
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Done."
End Sub

Open in new window


Let me know if you have any questions.
-Glenn
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 

Author Comment

by:MATO0618
ID: 41850975
Working great. I'd really like to have the output to be on another worksheet (tab). Other than that its perfect.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41851044
I'd really like to have the output to be on another worksheet (tab). Other than that its perfect.

That can be arranged.  Specify the sheet name and layout location and I can revise the code.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MATO0618
ID: 41851095
I've reattached the worksheet with the destination tab added (A1XX Output), and I'd like the data headers to start at Cell A1 as indicated in the file.

Thanks
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41851186
Here you go.  I've revised the code to paste the data into the [A1XX Output] sheet.  It's blank now; you can test it by pressing the "Transfer Data" button.

Regards,
-Glenn
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 

Author Closing Comment

by:MATO0618
ID: 41854343
Great help. Thanks
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question