Solved

Match Values and Return to Cell

Posted on 2016-10-19
6
32 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms office troubleshooting for users 8 35
macro for filtering the criteria and sending mail to all the data 8 27
Resizing every other graphic 3 13
increment numbers by 10 11 29
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now